Re: [GENERAL] Optimize Query

On 2/10/2016 6:38 PM, drum.lu...@gmail.com wrote: Sorry but I was unable to see the *n_quote_status = 0* I'm unable to see this variable anywhere in your two original queries, the SQL one, and the other ?? abbreviated thing, nor did you give any table definitions, so I'm not even sure what yo

Re: [GENERAL] Optimize Query

> > > FYI, it is always helpful (and polite) to state the PostgreSQL VERSION and > O/S for archive documentation purposes! > That's right. My mistake... I'm using PostgreSQL 9.2. > > Note that various postgresql.conf options, system memory & hardware also > play a factor here, in addition to cur

Re: [GENERAL] Optimize Query

On Wed, Feb 10, 2016 at 8:25 PM, drum.lu...@gmail.com wrote: > Hi all, > > I've got a slow query and I'm trying to make it faster. > > *New Query:* > > SELECT concat(client.company, ' ', client.name_first, ' ', >> client.name_last) AS customer, >>sum(COALESCE(bill_item.unit_price, billabl

[GENERAL] Optimize Query

Hi all, I've got a slow query and I'm trying to make it faster. *New Query:* SELECT concat(client.company, ' ', client.name_first, ' ', > client.name_last) AS customer, >sum(COALESCE(bill_item.unit_price, billable.unit_price, 0) * > bill_item.quantity) AS revenue, >sum(bill_item.

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

=?UTF-8?Q?Mart=c3=adn_Marqu=c3=a9s?= writes: > [ log_line_prefix %x frequently reports zero ] > <2016-02-10 17:41:19 EST [5729]: [1] xid=0 > db=data,user=postgres,app=psql,client=[local]>LOG: duration: 17.242 ms > statement: create table test_xid (id int); > <2016-02-10 17:41:21 EST [5729]: [2]

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

On 02/10/2016 02:58 PM, Martín Marqués wrote: Hi, I've been fooling around on a test environment where I wanted to run some PITR tests using recovery_target_xid. So I started setting up postgresql.conf with log_statement='ddl' (and 'mod' also) and the %x placeholder in log_line_prefix: Odd res

[GENERAL] Transaction ID not logged if no explicit transaction used

Hi, I've been fooling around on a test environment where I wanted to run some PITR tests using recovery_target_xid. So I started setting up postgresql.conf with log_statement='ddl' (and 'mod' also) and the %x placeholder in log_line_prefix: Odd result was that I always got a zero as the xid. S

Re: [GENERAL] pg_multixact issues

On Thu, Feb 11, 2016 at 11:05 AM, Alvaro Herrera wrote: > Thomas Munro wrote: > >> 4. You could look into whether all those multixacts with many member >> are really expected. (Large numbers of concurrent FK checks or >> explicit share locks on the same rows perhaps? A good proportion of >> thi

Re: [GENERAL] pg_multixact issues

Thomas Munro wrote: > 4. You could look into whether all those multixacts with many member > are really expected. (Large numbers of concurrent FK checks or > explicit share locks on the same rows perhaps? A good proportion of > this happened on one day this week I see.) I think maybe this patc

Re: [GENERAL] Catalog bloat (again)

As a follow-up, here's a portion of the nightly vacuum's logs, just want to confirm if my conclusions are right: INFO: vacuuming "pg_catalog.pg_attribute" INFO: scanned index "pg_attribute_relid_attnam_index" to remove 3014172 row versions DETAIL: CPU 0.20s/1.08u sec elapsed 3.72 sec. INFO:

Re: [GENERAL] pg_multixact issues

On Thu, Feb 11, 2016 at 7:52 AM, Kiriakos Georgiou wrote: > Hello, > > Our pg_multixact directory keeps growing. I did a "vacuum freeze” which > didn’t help. I also did a "vacuum full” which didn’t help either. > We had this condition with 9.3.4 as well. When I upgraded our cluster to > 9.4.5 (

Re: [GENERAL] Input data for column is not compatible with data type

yep.. thank you for your replly. The problem is a code problem. Not in the DB. Thank you. Lucas Possamai - kinghost.co.nz - DigitalOcean On 11 February 201

Re: [GENERAL] Input data for column is not compatible with data type

On 02/10/2016 12:11 PM, David G. Johnston wrote: On Wed, Feb 10, 2016 at 12:56 PM, drum.lu...@gmail.com mailto:drum.lu...@gmail.com>>wrote: Hi all I'm having this error message: Input data for column `%1$s` is not compatible with data type But I

Re: [GENERAL] Input data for column is not compatible with data type

On 02/10/2016 11:56 AM, drum.lu...@gmail.com wrote: Hi all I'm having this error message: Input data for column `%1$s` is not compatible with data type The above, from looking at the link below, looks like part of the query. If that is so I am guessing the back ticks are causing the problem.

Re: [GENERAL] Test CMake build

On 2/10/2016 2:50 PM, Andy Colson wrote: On 2/10/2016 2:45 PM, Andy Colson wrote: On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does th

Re: [GENERAL] Test CMake build

On 2/10/2016 2:45 PM, Andy Colson wrote: On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does the repository is on github: https://github.

Re: [GENERAL] Test CMake build

On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does the repository is on github: https://github.com/stalkerg/postgres_cmake The compilati

Re: [GENERAL] Test CMake build

> On 10 Feb 2016, at 19:09, Yury Zhuravlev wrote: > > Hello all. > Please test build Postgres using cmake. If you are of course interested. > Still not everything is ready but most of the work. Assembly instructions as > does the repository is on github: > https://github.com/stalkerg/postgres_c

Re: [GENERAL] Input data for column is not compatible with data type

Actually I do know which table is... it seems to be ja_notes. But I can't see any difference Lucas Possamai - kinghost.co.nz - DigitalOcean On 11 February

Re: [GENERAL] Input data for column is not compatible with data type

On Wed, Feb 10, 2016 at 12:56 PM, drum.lu...@gmail.com wrote: > Hi all > > I'm having this error message: > > Input data for column `%1$s` is not compatible with data type > > > But I was unable to find the table which is having this issue > > Would be possible someone help me with that? > Th

[GENERAL] Input data for column is not compatible with data type

Hi all I'm having this error message: Input data for column `%1$s` is not compatible with data type But I was unable to find the table which is having this issue Would be possible someone help me with that? Thanks Complete error link: https://bitbucket.org/snippets/lucaspossamai/7r9yX Qu

Re: [GENERAL] four template0 databases after vacuum

On 02/10/2016 07:19 AM, Kazuaki Fujikura wrote: Thank you for your comments. >Can you explain what your replication set up is? Streaming Replication. => masterslave1 (async) masterslave2 (async) >So are you doing the below on the master, the slaves or all? =>master Our current plan

Re: [GENERAL] PostgreSQL vs Firebird SQL

On Wed, Feb 10, 2016 at 01:14:42PM +0200, Achilleas Mantzios wrote: > >>About checksums in our office master DB that's a fine idea, too bad that > >>pg_upgrade doesn't cope with them > >I am sure you have considered "failing over" the master to an > >in-office slave which has got checksums turned

[GENERAL] pg_multixact issues

Hello, Our pg_multixact directory keeps growing. I did a "vacuum freeze” which didn’t help. I also did a "vacuum full” which didn’t help either. We had this condition with 9.3.4 as well. When I upgraded our cluster to 9.4.5 (via plain sql dump and load) as expected the issue was resolved but

Re: [GENERAL] PostgreSQL vs Firebird SQL

On 02/10/2016 09:24 AM, David G. Johnston wrote: On Wed, Feb 10, 2016 at 10:08 AM, John R Pierce mailto:pie...@hogranch.com>>wrote: On 2/10/2016 8:51 AM, Pierre Chevalier Géologue wrote: PPS: how should I behave on this list: should I systematically "reply to all", or just "reply" t

[GENERAL] Test CMake build

Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does the repository is on github: https://github.com/stalkerg/postgres_cmake The compilation will be enough (tests even better). I nee

Re: [GENERAL] PostgreSQL vs Firebird SQL

On Wed, Feb 10, 2016 at 10:08 AM, John R Pierce wrote: > On 2/10/2016 8:51 AM, Pierre Chevalier Géologue wrote: > > PPS: how should I behave on this list: should I systematically "reply to > all", or just "reply" to the list? I'm used to a number of mailing lists > where a simple "reply" automat

Re: [GENERAL] PostgreSQL vs Firebird SQL

On 2/10/2016 8:51 AM, Pierre Chevalier Géologue wrote: PPS: how should I behave on this list: should I systematically "reply to all", or just "reply" to the list? I'm used to a number of mailing lists where a simple "reply" automatically replies to the list, and the rule obliges you to *only*

Re: [GENERAL] PostgreSQL vs Firebird SQL

> Is this running as an embedded engine or a standalone server? One thing > about Firebird is that since it is embeddable, in that mode other > application bugs could corrupt the database. In the other case, I would > expect you may want to run hardware diagnostics to rule out hardware > problems

Re: [GENERAL] PostgreSQL vs Firebird SQL

Hello, Le 10/02/2016 08:43, Chris Travers a écrit : I have never seen database corruption on PostgreSQL that was not a result of either: 1. Use cases WAY out of the ordinary (and then only years ago and I reported a bug on this and it was very quickly fixed) 2. Hardware problems 3. Heat manageme

Re: [GENERAL] PostgreSQL vs Firebird SQL

Em 10/02/2016 13:32, Andy Colson escreveu: On 2/9/2016 10:10 PM, ioan ghip wrote: I have a Firebird SQL database running on one of my servers which has about 50k inserts, about 100k updates and about 30k deletes every day. There are about 4 million records in 24 tables. I have a bunch of stored

Re: [GENERAL] PostgreSQL vs Firebird SQL

On 2/9/2016 10:10 PM, ioan ghip wrote: I have a Firebird SQL database running on one of my servers which has about 50k inserts, about 100k updates and about 30k deletes every day. There are about 4 million records in 24 tables. I have a bunch of stored procedures, triggers, events and views that

Re: [GENERAL] four template0 databases after vacuum

Thank you for your comments. >Can you explain what your replication set up is? Streaming Replication. => masterslave1 (async) masterslave2 (async) >So are you doing the below on the master, the slaves or all? =>master Our current plan at this moment is - Upgrade 9.1.19 from 9.1.6 at the

Re: [GENERAL] ERROR: missing FROM-clause entry for table

On 2/10/2016 1:33 AM, bigkev wrote: Yes, c.start_time is a timestamp. It is really late where I am, so I apologize if I'm being dense... My goal is to list events (calls) which are scheduled on fortnightly basis. The query works with hard coded values, but I need to generate results for days

Re: [GENERAL] PostgreSQL vs Firebird SQL

On 10/02/2016 12:40, Karsten Hilbert wrote: On Wed, Feb 10, 2016 at 10:34:53AM +0200, Achilleas Mantzios wrote: PG on tankers: About checksums in our office master DB that's a fine idea, too bad that pg_upgrade doesn't cope with them I am sure you have considered "failing over" the master to

Re: [GENERAL] PostgreSQL vs Firebird SQL

On Wed, Feb 10, 2016 at 10:34:53AM +0200, Achilleas Mantzios wrote: PG on tankers: > About checksums in our office master DB that's a fine idea, too bad that > pg_upgrade doesn't cope with them I am sure you have considered "failing over" the master to an in-office slave which has got checksums

Re: [GENERAL] ERROR: missing FROM-clause entry for table

Yes, c.start_time is a timestamp. My goal is to list events (calls) which are scheduled on fortnightly basis. The query works with hard coded values, but I need to generate results for days beginning at the start_time and ending with the end_time.: eg: 2016-02-06 2016-02-07 2016-02-08 | Some acco

Re: [GENERAL] PostgreSQL vs Firebird SQL

On 10/02/2016 06:10, ioan ghip wrote: I have a Firebird SQL database running on one of my servers which has about 50k inserts, about 100k updates and about 30k deletes every day. There are about 4 million records in 24 tables. I have a bunch of stored procedures, triggers, events and views that I

Re: [GENERAL] ERROR: missing FROM-clause entry for table

On 2/9/2016 11:11 PM, bigkev wrote: I am receiving this error for the query pasted below. Is the LEFT JOIN on the table not enough? What needs to happen here? I am guess something to do with derived tables http://pastie.org/10715876 Your error is in the reference to c.start_time, c.end_time.