Re: [GENERAL] logs encoding problem Windows

2012/12/7 Tom Lane > > Postmaster log messages are written in whatever the database_encoding > is, so if you've got multiple databases with different encodings, the > encoding in the log will be inconsistent. Thanks for your answer Tom. but... all DBs are encoded in UTF8 (SELECT encoding FROM pg

Re: [GENERAL] query performance, though it was timestamps,maybe just table size?

On Sun, Dec 9, 2012 at 7:16 PM, Jeff Janes wrote: > The obvious difference is that this one finds all 5 buffers it needs > in buffers already, while the first one had to read them in. So this > supports the idea that your data has simply grown too large for your > RAM. > > Cheers, > > Jeff > J

[GENERAL] Out of memory error

Hello, In a database of one of our customers we sometimes get out of memory errors. Below I have copy pasted one of these very long messages. The error doesn't always occur, when I copy paste the query and run it manually it works. The current server is an OpenSUSE 12.2 with postgresql 9.2.1 (we

Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master

Hello,   Does anyone else have any insight or information around this issue?  I can't find anything out there on the web.   If it's a simple incompatability then fine, but I'd still like to understand why the indexes on the secondary node corrupt when the data seems to be replicated without issu

[GENERAL] logcheck compatible daily logfile rotation?

Hello I'm trying to figure out how to configure a logcheck compatible logfile rotation i.e. where a) the current logfile always has a fixed name and old files are named like "*.1", "*.2.gz" etc. and b) the current logfile is not truncated but newly created (or else logcheck always warns ab

Re: [GENERAL] Problem with aborting entire transactions on error

Please reply-to the list, not just myself. > -Original Message- > From: Zbigniew [mailto:zbigniew2...@gmail.com] > Sent: Monday, December 10, 2012 6:26 AM > To: David Johnston > Subject: Re: [GENERAL] Problem with aborting entire transactions on error > > 2012/12/10, David Johnston : > >

Re: [GENERAL] Problem with aborting entire transactions on error

Zbigniew, 10.12.2012 04:20: Yes, I read about using "savepoints" - but I think we agree, it's just cumbersome workaround - and not real solution, I disagree. It might be a bit cumbersome, but it *is* a proper solution to the problem - not a workaround. Thomas -- Sent via pgsql-general

Re: [GENERAL] Problem with aborting entire transactions on error

On Tue, Dec 11, 2012 at 1:15 AM, David Johnston wrote: >> -Original Message- >> From: Zbigniew [mailto:zbigniew2...@gmail.com] >> Sent: Monday, December 10, 2012 6:26 AM >> To: David Johnston >> Subject: Re: [GENERAL] Problem with aborting entire transactions on error >> >> No idea, why co

Re: [GENERAL] Problem with aborting entire transactions on error

On 12/10/2012 06:52 AM, Zbigniew wrote: 2012/12/10, David Johnston : I've got a feeling, that all you have to say, is: "if this is the way it is, it means, that this is good, and shouldn't be changed". You are unable to explain, why - just the "common belief" etc. is your rationale (while it

Re: [GENERAL] Problem with aborting entire transactions on error

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Zbigniew > Sent: Monday, December 10, 2012 9:53 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Problem with aborting entire transactions on error > >

Re: [GENERAL] Problem with aborting entire transactions on error

Chris Angelico, 10.12.2012 16:47: The other part of the problem is that you're using transactions for something other than transactional integrity. You're batching up transactions in order to improve performance. That's certainly possible, but you're using the database "oddly", so you can't expec

Re: [GENERAL] Out of memory error

Eelke Klein writes: > In a database of one of our customers we sometimes get out of memory > errors. Below I have copy pasted one of these very long messages. > The error doesn't always occur, when I copy paste the query and run it > manually it works. The memory map doesn't look out of the ordin

Re: [GENERAL] Problem with aborting entire transactions on error

> if { no error during query } { > do it > } else { > withdraw this one > if { ROLLBACK_ON_ERROR } { >rollback entire transaction > } > } I fear that this is no-where near as simple as it's been taken for, and without looking at the code, I would imagine that this would touch so

[GENERAL] ANALYZE and partitioning

Hello all! I have a table with weekly partitions, going back about 9 months. This is a rollup table, and I update the values in the table once per day, plus a final refresh for the previous week on Monday. The parent table has no rows, nor should it contain any. The rollup script does this: B

[GENERAL] Import raster file with 3 bands

Dear all, I import with sucess a 3 band image to PostgreSQL 9.1 with the raster2pgsql in Ubuntu 12.04LTS. But when I try to visualize in another software I can't see the image and it looks like the values from the other bands glue only in the first band. Do you know if there’s any problem wit

Re: [GENERAL] Query and index ... unexpected result need advice.

On Sun, Dec 9, 2012 at 10:59 PM, Condor wrote: > On 2012-12-10 00:31, Jeff Janes wrote: >> >> On Sat, Dec 8, 2012 at 5:54 AM, Condor wrote: >> >>> create index clients_tbl_firstname_idx on clients_tbl using btree >>> (firstname >>> COLLATE "bg_BG" text_pattern_ops); >> >> >> I don't understand w

Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master

On Wed, Dec 5, 2012 at 5:17 AM, James Cowell wrote: > I'm using pg_bulkload to load large amounts of CSV data into a postgres > database hourly. > > This database is replicated to a second node. > > Whenever a bulk load happens the indexes on the updated tables on the > secondary node corrupt and

Re: [GENERAL] Problem with aborting entire transactions on error

On Sun, Dec 9, 2012 at 8:20 PM, Zbigniew wrote: > Hello, > > As I read while googling the web, many people complained about this > before. Couldn't it be made optional (can be even with "default ON")? > I understand, that there are situations, when it is a must - for > example, when the rest of qu

Re: [GENERAL] When is archive_cleanup called?

Le 2012-12-09 à 16:32, Jeff Janes a écrit : > On Fri, Nov 30, 2012 at 6:44 AM, François Beausoleil > wrote: >> >> How come no new restart points were achieved? I had 4008 WAL archives on my >> slave. I expected them to be removed as streaming replication progressed. >> Are restart points prev

[GENERAL] 9.2.2 RH, Fedora

Hi all, Could somebody tell if or when 9.2.2 packages will arrive to the PGDG yum repository? Thanks, Grazvydas

Re: [GENERAL] 9.2.2 RH, Fedora

Hi, On Mon, 2012-12-10 at 20:09 +0200, Gražvydas Valeika wrote: > Could somebody tell if or when 9.2.2 packages will arrive to the PGDG yum > repository? Pretty soon. Packages are ready, they are under rsync process. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://

Re: [GENERAL] Problem with aborting entire transactions on error

2012/12/10, Scott Marlowe : > Databases aren't as simple as you imagine. What you're basically > asking for from the pg engine is for it to enclose every insert into a > subtransaction (i.e. set a savepoint) to check for an error. No, I'm not. It's able (I mean Postgres) to detect an error, and

Re: [GENERAL] Problem with aborting entire transactions on error

On Mon, Dec 10, 2012 at 11:29 AM, Zbigniew wrote: > 2012/12/10, Scott Marlowe : > >> Databases aren't as simple as you imagine. What you're basically >> asking for from the pg engine is for it to enclose every insert into a >> subtransaction (i.e. set a savepoint) to check for an error. > > No, I

[GENERAL] Literal NUL using COPY TO

Can one use COPY TO stdout to output a literal NUL byte as the delimiter? # \encoding UTF8 # copy (select * from q limit 1) to stdout with (format csv, delimiter E'\000', header false); ERROR: invalid byte sequence for encoding "UTF8": 0x00 # \encoding sql_ascii # copy (select * from q limit 1)

Re: [GENERAL] Problem with aborting entire transactions on error

2012/12/10 Thomas Kellerer > > Zbigniew, 10.12.2012 04:20: >> >> Yes, I read about using "savepoints" - but I think we agree, >> it's just cumbersome workaround - and not real solution, > > > It might be a bit cumbersome, but it *is* a proper solution to the problem - > not a workaround. Writing

Re: [GENERAL] Literal NUL using COPY TO

=?iso-8859-1?Q?Fran=E7ois_Beausoleil?= writes: > Can one use COPY TO stdout to output a literal NUL byte as the delimiter? No. Postgres doesn't allow embedded NULs in text strings in general. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@post

[GENERAL] to_tsquery and to_tsvector .. problem with Y

Hi all - We are using to_tsquery and to_tsvector quite a bit. I have noticed that it is not searching for "Y". is there some settings I have to do make sure when search for string that has "Y" to return the correct results? Thanks for the help Regards

Re: [GENERAL] Problem with aborting entire transactions on error

Zbigniew wrote: > No idea, is it simple or very complicated. But if it is simple - > why not? When I first started using PostgreSQL I was used to a database product which would roll back an entire statement on failure, but leave the transaction open for another try. This is handy for interactive

Re: [GENERAL] Problem with aborting entire transactions on error

Caveat: I am not a PostgreSQL hacker, and have not looked into its internals at all, though I've read a number of excellent articles and blog posts on some of its features (TOAST, HOT updates, MVCC, etc). I'm a programmer who has made use of PG from a number of languages, and formed a strong opinio

Re: [GENERAL] to_tsquery and to_tsvector .. problem with Y

On Mon, Dec 10, 2012 at 12:38 PM, akp geek wrote: > We are using to_tsquery and to_tsvector quite a bit. I have > noticed that it is not searching for "Y". is there some settings I have to > do make sure when search for string that has "Y" to return the correct > results? Could you pl

Re: [GENERAL] to_tsquery and to_tsvector .. problem with Y

På mandag 10. desember 2012 kl. 21:38:45, skrev akp geek : Hi all -            We are using to_tsquery and to_tsvector quite a bit.  I have noticed that it is not searching for "Y".   is there some settings I have to do make sure when search for string that has "Y" to return th

[GENERAL] execution plan is wrong, or the query ?

Folks, I have a following query that used to work as intended on 8.3.5 : SELECT COUNT(*) FROM jiveDeployRequest dr LEFT JOIN jiveDeployType dt ON dr.deployTypeId = dt.deployTypeId LEFT JOIN jiveDeployStatus ds ON dr.deployStatusId = ds.deployStatusId LEFT OUTER JOIN jiveCustomerInstallationDeplo

Re: [GENERAL] Problem with aborting entire transactions on error

On Mon, Dec 10, 2012 at 06:29:49PM +, Zbigniew wrote: > > No, I'm not. > > It's able (I mean Postgres) to detect an error, and it's able to react > on error. "What I'm basically asking for" is an option to change its > reaction; that's all. Just to change a bit the thing already exisiting > -

Re: [GENERAL] Problem with aborting entire transactions on error

On Mon, Dec 10, 2012 at 05:02:37PM +0100, Thomas Kellerer wrote: > Just as a side-note: Oracle also allows you to commit a "transaction" even if > some of the statements failed True. I always thought that was a bizarre bug. Certainly, it would be the day you did BEGIN; INSERT INTO sale $bunch

[GENERAL] Locking issue

Hi everyone, I am running 'PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 64-bit' on a Windows Server and I am having some Locking issues. Maybe anyone can let me know what is wrong with my example: Imagine that we have two tables (t_users and t_records) t_users contains 1 row per

Re: [GENERAL] Problem with aborting entire transactions on error

"Kevin Grittner" writes: > Zbigniew wrote: >> No idea, is it simple or very complicated. But if it is simple - >> why not? > When I first started using PostgreSQL I was used to a database > product which would roll back an entire statement on failure, but > leave the transaction open for another

Re: [GENERAL] Problem with aborting entire transactions on error

Hi, On 11 December 2012 06:25, Виктор Егоров wrote: > On the other hand, it is possible to write "whenever sqlerror > continue;" and this will make ORACLE to process all the statements > inide the script, ignoring all errors. This is a general feature, > available not only for sqlplus scripts — a

Re: [GENERAL] execution plan is wrong, or the query ?

Alex Burkoff writes: > I have a following query that used to work as intended on 8.3.5 : > ... > After upgrade to 9.2 the query doesn't return the same results any more, and > the execution plan has changed : You'd need to provide a self-contained test case if you want an informed opinion on thi

[GENERAL] large database

Hi, I've recently inherited a project that involves importing a large set of Access mdb files into a Postgres or MySQL database. The process is to export the mdb's to comma separated files than import those into the final database. We are now at the point where the csv files are all created and am

Re: [GENERAL] large database

Hi, On 11 December 2012 07:26, Mihai Popa wrote: > First, the project has been started using MySQL. Is it worth switching > to Postgres and if so, which version should I use? You should to consider several things: - do you have in-depth MySQL knowledge in you team? - do you need any sql_mode "fe