Re: [GENERAL] SQLSTATE[08006] [7] server closed the connection unexpectedly

Yvon Thoraval wrote: >> You may want to set log_statement='all' in postgresql.conf > i did this change , restart PostgreSQL and test again, with the same error, > however postgres log > didn't report any error, as far as i understand the log : > > 2012-09-14 18:43:14 CEST LOG: received fast shu

Re: [GENERAL] application for postgres Log

Arvind Singh wrote: > I have come across a very peculiar situation. > > We have a postgres installation 9.0. It was installed last year. > > But we started implementation on it, just recently. > > And therefore the need to develop a Logparser application. > > During our preliminary parsing , Wh

Re: [GENERAL] On Ubuntu 12.04 i do have two psql one of those isn't working

On 17/09/2012 07:15, Yvon Thoraval wrote: > > I'm testing my second PostgreSQL install on Xunutu 12.04 after a disk crash. > > Everything went well using command line where i was able, after config > and setup, to log into a db using : > $ psql -U yt -d yt_tests > > Then i wanted to test postgr

[GENERAL] Slow counting still true?

Release 9.2 should increase count(*) performance. Is this wiki page still valid? http://wiki.postgresql.org/wiki/Slow_Counting Please update the content. Thank you, Thomas Güttler -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- Sent via pgsql

[GENERAL] foreign key from array element

Hello the list, recently I'm cooking a database for an application, that I feel is best implemented with tables containing arrays. I have found some related info here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php although the initial advice in that thread was: not to use ar

Re: [GENERAL] Slow counting still true?

On Mon, Sep 17, 2012 at 1:07 AM, Thomas Guettler wrote: > Release 9.2 should increase count(*) performance. Is this wiki page still > valid? > > http://wiki.postgresql.org/**wiki/Slow_Counting > > Please update the content. > > As I understand it, co

Re: [GENERAL] Index creation takes more time?

Yes, thank you, I did notice it, but I decided to wait a week to the next archive schedule, to see if the problem persists, especially since the previous time ran with relatively low disk space because we kept the old database files around. We have removed them during the week. Unfortunately, t

Re: [GENERAL] SQLSTATE[08006] [7] server closed the connection unexpectedly

2012/9/17 Albe Laurenz > Yvon Thoraval wrote: > >> You may want to set log_statement='all' in postgresql.conf > > > i did this change , restart PostgreSQL and test again, with the same > error, however postgres log > > didn't report any error, as far as i understand the log : > > > > > > Well,

Re: [GENERAL] On Ubuntu 12.04 i do have two psql one of those isn't working

2012/9/17 Raymond O'Donnell > > The first isn't a "real" psql - it's a symlink to pg_wrapper, which is > how Debian and Ubuntu allow you to run different Postgres versions and > clusters on the same machine. You call the client program (psql, > pg_dump, etc) you want as usual, but pass it the --c

Re: [GENERAL] foreign key from array element

On 09/17/2012 04:46 PM, Rafal Pietrak wrote: Hello the list, recently I'm cooking a database for an application, that I feel is best implemented with tables containing arrays. I have found some related info here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php although the in

[GENERAL] Can a view use a schema search_path?

I have the feeling the answer is no, but I would like an authoritative answer before I give up. My plan was to have two schemas: one for the live data, and one for staging, training, and testing. Both schemas would have identically-named tables. I wanted to create a single view in the public sch

Re: [GENERAL] foreign key from array element

On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: > On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] > There was some quite recent discussion on ELEMENT foreign keys on the > -hackers list. Try searching pgsql-hackers for ELEMENT foreign key. > > See: >https://commitfest.pos

Re: [GENERAL] Can a view use a schema search_path?

Adam Mackler, 17.09.2012 11:06: I have the feeling the answer is no, but I would like an authoritative answer before I give up. My plan was to have two schemas: one for the live data, and one for staging, training, and testing. Both schemas would have identically-named tables. I wanted to crea

Re: [GENERAL] Can a view use a schema search_path?

On Mon, Sep 17, 2012 at 7:06 PM, Adam Mackler wrote: > Am I correct in concluding that there's no way to have a single view in the > public schema that selects data from tables in different other schemas > depending on my search_path at the time I execute a query involving that > view? I don't th

Re: [GENERAL] Can a view use a schema search_path?

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Chris Angelico > Sent: Monday, September 17, 2012 9:12 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Can a view use a schema search_path? > > On Mon

Re: [GENERAL] Bad pg_dump error message

On 2012-09-11, Mike Christensen wrote: > Is the TAR format just the raw SQL commands, just tar'ed and then sent > over the wire? It'd be cool if there was some compressed "binary" > backup of a database that could be easily downloaded, or even better, > a way to just move an entire database betw

Re: [GENERAL] Compressed binary field

Em 17/09/2012 00:17, Jeff Janes escreveu: On Tue, Sep 11, 2012 at 9:34 AM, Edson Richter wrote: No, there is no problem. Just trying to reduce database size forcing these fields to compress. Actual database size = 8Gb Backup size = 1.6Gb (5x smaller) Seems to me (IMHO) that there is room for i

Re: [GENERAL] On Ubuntu 12.04 i do have two psql one of those isn't working

On 17/09/2012 12:52, Yvon Thoraval wrote: > > then, the php link to the client library is faulty. What makes you think this? Can you show the output from a PHP script (not psql)? - something simple like... $conn = pg_connect(); $rs = pg_query(); ...just to establish whether the connect

Re: [GENERAL] Can a view use a schema search_path?

On 17 September 2012 11:06, Adam Mackler wrote: > I have the feeling the answer is no, but I would like an authoritative > answer before I give up. > > My plan was to have two schemas: one for the live data, and one for staging, > training, and testing. Both schemas would have identically-named t

[GENERAL] Get time for each operation in "Explain..."

Is there any flag I can set in PostgreSQL to get the real time spent in each step on "Explain"? Is like "Explain Execute" that will get time for each step after real execution. I know there is the "cost" attribute, but seems to me that the "projected cost" is not alwa

Re: [GENERAL] Get time for each operation in "Explain..."

Hello use EXPLAIN ANALYZE chef=# explain ANALYZE select count(*) from d_items_itmaum_aan2ii80lhab3x0 ; QUERY PLAN - Aggreg

Re: [GENERAL] Can a view use a schema search_path?

Another solution would be to create a view based on a function and pass the schema name to the function. But,  I think you have to rethink your server configuration and may be whole development environment. One server for  live data and staging, testing , training ... I found that really dange

Re: [GENERAL] Get time for each operation in "Explain..."

Perfect! As I did suspect, I need more education :-) Regards, Edson Carlos Ericksson Richter SimKorp Informática Ltda

Re: [GENERAL] Slow counting still true?

On Mon, Sep 17, 2012 at 1:07 AM, Thomas Guettler wrote: > Release 9.2 should increase count(*) performance. Is this wiki page still > valid? Even with index only scans, it still has to walk through every tuple, even if they are only index tuples not table tuples. So nothing has fundamentally cha

Re: [GENERAL] Planner forces seq scan when select without quoting its values

This line from the EXPLAIN output suggests that the rule is causing a seq scan to be used instead on an index scan: ... WHERE ... a.archiveset = new.archiveset AND a.fileid <> new.fileid. When I replace the "new." with actual integers: ... WHERE ... a.archiveset = 10003 AND a.filei

Re: [GENERAL] Slow counting still true?

Em 17/09/2012 06:13, Chris Travers escreveu: On Mon, Sep 17, 2012 at 1:07 AM, Thomas Guettler > wrote: Release 9.2 should increase count(*) performance. Is this wiki page still valid? http://wiki.postgresql.org/wiki/Slow_Counting Please update the c

[GENERAL] Why is the wrong index used? (with "gist" index)

Hello Just because I added a "LIMIT 1" to the following query, PostgreSQL decided to use a different index which gives a far worse performance :-( Probably it gets a bit confused because I use the custom index from the prefix extension (https://github.com/dimitri/prefix/blob/master/README.txt). I

Re: [GENERAL] Why is the wrong index used? (with "gist" index)

Christian Hammers writes: > Just because I added a "LIMIT 1" to the following query, PostgreSQL decided to > use a different index which gives a far worse performance :-( The problem is the off-by-four-orders-of-magnitude rowcount estimate (20949 rows estimated to match, only 2 actually do). You

Re: [GENERAL] Slow counting still true?

If the numbers do not have to be exact the web applications could use the cardinality estimates stored in the system tables. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Edson Richter Sent: Monday, September 17, 2012 9:14 AM To: pgsql-general@

Re: [GENERAL] bgwriter and pg_locks

On Wed, Sep 12, 2012 at 8:27 PM, Ben Chobot wrote: > In an attempt to get a hackfix for > http://pgfoundry.org/tracker/index.php?func=detail&aid=1011203&group_id=1000411&atid=1376, > I'm wonder if it's true that, when looking at pg_locks, the only pid which > will have virtualxid = '1/1' and vi

[GENERAL] Official C++ API for postgresql?

Hello, Tell me please, whether there is an official C++ API for postgresql? Thanks, -- Regards, niXman ___ Dual-target(32 & 64 bit) MinGW compilers for 32 and 64 bit Windows: http://sourceforge.net/projects/mingwbuilds/ -- Sent via pgsql-genera

Re: [GENERAL] Official C++ API for postgresql?

On Mon, Sep 17, 2012 at 10:58:30PM +0400, niXman wrote: > Hello, > > Tell me please, whether there is an official C++ API for postgresql? http://pqxx.org/development/libpqxx/ > > Thanks, > > -- > Regards, > niXman -Ryan Kelly -- Sent via pgsql-general mailing list (pgsql-general@postgresql

[GENERAL] log_destination = csvlog

Dear all, I've started collecting log information in CSV format, but I need a way to customize it. Problem is that I'm collecting slow statements, and the statements have \n (newline) character, and the piece of 2012-09-17 14:25:33.150 BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d

[GENERAL] log_destination = csvlog, line breaks interfere in analysis

Dear all, I've started collecting log information in CSV format, but I need a way to customize it. Problem is that I'm collecting slow statements, and the statements have \n (newline) character, and the piece of 2012-09-17 14:25:33.150 BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d

Re: [GENERAL] Official C++ API for postgresql?

On Sep 17, 2012, at 11:58 AM, niXman wrote: > Hello, > > Tell me please, whether there is an official C++ API for postgresql? http://pqxx.org/development/libpqxx/ is the main C++ specific library. But you can also use libpq from C++, and for simpler work it's often easier than libpqxx. There

Re: [GENERAL] Official C++ API for postgresql?

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Atkins Sent: Monday, September 17, 2012 12:23 PM To: pgsql-general@postgresql.org General Subject: Re: [GENERAL] Official C++ API for postgresql? On Sep 17, 2012, at

Re: [GENERAL] foreign key from array element

On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak wrote: > On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: >> On 09/17/2012 04:46 PM, Rafal Pietrak wrote: > [--] >> There was some quite recent discussion on ELEMENT foreign keys on the >> -hackers list. Try searching pgsql-hackers f

Re: [GENERAL] log_destination = csvlog

Em 17/09/2012 16:32, Ryan Kelly escreveu: On Mon, Sep 17, 2012 at 04:20:14PM -0300, Edson Richter wrote: Dear all, I've started collecting log information in CSV format, but I need a way to customize it. Problem is that I'm collecting slow statements, and the statements have \n (newline) charac

Re: [GENERAL] log_destination = csvlog

On Mon, Sep 17, 2012 at 04:20:14PM -0300, Edson Richter wrote: > Dear all, > > I've started collecting log information in CSV format, but I need a > way to customize it. > Problem is that I'm collecting slow statements, and the statements > have \n (newline) character, and the piece of > > 2012-0

Re: [GENERAL] Official C++ API for postgresql?

2012/9/17 Ryan Kelly: > http://pqxx.org/development/libpqxx/ Ah, okay. Thank you Ryan. -- Regards, niXman ___ Dual-target(32 & 64 bit) MinGW compilers for 32 and 64 bit Windows: http://sourceforge.net/projects/mingwbuilds/ -- Sent via pgsql-g

Re: [GENERAL] Official C++ API for postgresql?

2012/9/17 Ryan Kelly: > http://pqxx.org/development/libpqxx/ Last question... According to the link provided by you, it is said that libpqxx comes with postgres. But in the archive with postgres sources I haven't find libpqxx. -- Regards, niXman

Re: [GENERAL] Official C++ API for postgresql?

On Mon, Sep 17, 2012 at 11:52:13PM +0400, niXman wrote: > 2012/9/17 Ryan Kelly: > > > http://pqxx.org/development/libpqxx/ > > Last question... > According to the link provided by you, it is said that libpqxx comes > with postgres. But in the archive with postgres sources I haven't find > libpqxx

Re: [GENERAL] Official C++ API for postgresql?

2012/9/17 Ryan Kelly: > It is not provided with postgres. It is a separate library. Hmm.. >From link provided by you: > This library works on top of the C-level API library, libpq. It comes with > postgres. -- Regards, niXman ___ Dual-target(32

Re: [GENERAL] Official C++ API for postgresql?

On Mon, Sep 17, 2012 at 1:58 PM, niXman wrote: > Hello, > > Tell me please, whether there is an official C++ API for postgresql? Technically speaking, the only official C++ api is libpq, which, while a C API, is C++ compatible. libpxx is a set of wrappers to libpq that will give you C++ classes

Re: [GENERAL] Official C++ API for postgresql?

On 09/17/2012 12:55 PM, Ryan Kelly wrote: On Mon, Sep 17, 2012 at 11:52:13PM +0400, niXman wrote: 2012/9/17 Ryan Kelly: http://pqxx.org/development/libpqxx/ Last question... According to the link provided by you, it is said that libpqxx comes with postgres. But in the archive with postgres s

Re: [GENERAL] log_destination = csvlog, line breaks interfere in analysis

... Sorry, by mistake I've duplicated the mail. -- 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] Official C++ API for postgresql?

2012/9/17 Adrian Klaver: > The it refers to libpq not libpqxx. I understang. Sorry.. -- Regards, niXman ___ Dual-target(32 & 64 bit) MinGW compilers for 32 and 64 bit Windows: http://sourceforge.net/projects/mingwbuilds/ -- Sent via pgsql-gene

Re: [GENERAL] unicode searches failing that use % and LIKE operators

I had the same problem. I solved it by doing query using the pattern matching operator ~*. I paste an example query select id_edb,transcription from epigraph where transcription ~* 'εἰρήνῃ' and it works best Fabio

Re: [GENERAL] log_destination = csvlog

Em 17/09/2012 16:32, Ryan Kelly escreveu: On Mon, Sep 17, 2012 at 04:20:14PM -0300, Edson Richter wrote: Dear all, I've started collecting log information in CSV format, but I need a way to customize it. Problem is that I'm collecting slow statements, and the statements have \n (newline) charac

Re: [GENERAL] Official C++ API for postgresql?

On 09/17/12 12:26 PM, Dann Corbit wrote: Not to mention the standards based drivers such as ODBC. the great thing about standards is there are so many to choose from. :) ODBC, however, is, IMHO, awful. -- john r pierceN 37, W 122 santa cruz ca

Re: [GENERAL] Official C++ API for postgresql?

On 09/17/12 12:57 PM, niXman wrote: 2012/9/17 Ryan Kelly: >It is not provided with postgres. It is a separate library. Hmm.. >From link provided by you: >This library works on top of the C-level API library, libpq. It comes with postgres. I suspect that is saying that libpq comes with pos

[GENERAL] when was a db/schema created ?

Is there a way to determine when a DB was created (create database...)? Thanks in advance.

[GENERAL] RFE: Column aliases in WHERE clauses

It would be nice if PostgreSQL supported column aliases in WHERE clauses, eg: SELECT left(value, 1) AS first_letter FROM some_table WHERE first_letter > 'a'; Is this the proper mailing list for such feature requests? Thanks in advance, Daniel Serodio -- Sent via pgsql-general mailing list (p

Re: [GENERAL] when was a db/schema created ?

2012/9/17 Gauthier, Dave > Is there a way to determine when a DB was created (create database...)?** > ** > > Thanks in advance. > Dear Dave, You can configure the "log_statement" [1] GUC to log DDL statements. Regards, [1] http://www.postgresql.org/docs/current/interactive/runtime-confi

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: > It would be nice if PostgreSQL supported column aliases in WHERE > clauses, eg: > > SELECT left(value, 1) AS first_letter > FROM some_table > WHERE first_letter > 'a'; > > Is this the proper mailing list for such feature re

Re: [GENERAL] log_destination = csvlog

On 18/09/12 08:45, Edson Richter wrote: Em 17/09/2012 16:32, Ryan Kelly escreveu: On Mon, Sep 17, 2012 at 04:20:14PM -0300, Edson Richter wrote: Dear all, I've started collecting log information in CSV format, but I need a way to customize it. Problem is that I'm collecting slow statements, an

Re: [GENERAL] Official C++ API for postgresql?

On Tue, Sep 18, 2012 at 5:56 AM, Adrian Klaver wrote: > I think the confusing part is: > > > "This library works on top of the C-level API library, libpq. It comes with > postgres" > > The it refers to libpq not libpqxx. Sounds to me like a wording change might be in order - perhaps "... libpq, w

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Sep 17, 2012, at 17:42, Ryan Kelly wrote: > On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: >> It would be nice if PostgreSQL supported column aliases in WHERE >> clauses, eg: >> >> SELECT left(value, 1) AS first_letter >> FROM some_table >> WHERE first_letter > 'a'; >

Re: [GENERAL] RFE: Column aliases in WHERE clauses

Ryan Kelly wrote: On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: It would be nice if PostgreSQL supported column aliases in WHERE clauses, eg: SELECT left(value, 1) AS first_letter FROM some_table WHERE first_letter> 'a'; Is this the proper mailing list for such featu

Re: [GENERAL] when was a db/schema created ?

On Mon, Sep 17, 2012 at 2:16 PM, Gauthier, Dave wrote: > Is there a way to determine when a DB was created (create database...)? If you don't have the CREATE DATABASE statement in your logs, you could try SELECT oid FROM pg_database WHERE datname = 'name of your database'; Then check the crea

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Mon, Sep 17, 2012 at 3:10 PM, Daniel Serodio (lists) wrote: > Ryan Kelly wrote: > > On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: > > It would be nice if PostgreSQL supported column aliases in WHERE > clauses, eg: > > SELECT left(value, 1) AS first_letter > FROM some_t

Re: [GENERAL] Official C++ API for postgresql?

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Monday, September 17, 2012 1:32 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Official C++ API for postgresql? On 09/17/12 12:26 PM, Dann C

Re: [GENERAL] RFE: Column aliases in WHERE clauses

Mike Christensen writes: > This definitely makes sense in the context of aggregation, but I'm > wondering if the same argument applies in the use case originally > posted: > SELECT left(value, 1) as first_letter > FROM some_table > WHERE first_letter > 'a'; > Obviously, you can write this as: >

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane wrote: > Mike Christensen writes: >> This definitely makes sense in the context of aggregation, but I'm >> wondering if the same argument applies in the use case originally >> posted: > >> SELECT left(value, 1) as first_letter >> FROM some_table >> WHERE

Re: [GENERAL] Index creation takes more time?

On Mon, Sep 17, 2012 at 4:07 AM, Herouth Maoz wrote: > Yes, thank you, I did notice it, but I decided to wait a week to the next > archive schedule, to see if the problem persists, especially since the > previous time ran with relatively low disk space because we kept the old > database files arou

[GENERAL] State of the art for foreign keys to child tables?

What's the state of the art for foreign keys on child tables? My use case is this: CREATE TABLE parties(party_id serial primary key); CREATE TABLE positions( PRIMARY KEY(party_id) ) INHERITS(parties); CREATE TABLE organizations( PRIMARY KEY(party_id) ) INHERITS(parties); CREATE TABLE party_names(

Re: [GENERAL] log_destination = csvlog, line breaks interfere in analysis

On 09/18/2012 03:21 AM, Edson Richter wrote: 2012-09-17 14:25:33.150 BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d2c,1,"SELECT",2012-09-15 16:14:51 BRT,10/204822,0,LOG,0,"duração: 505.494 ms executar : select A, B from C join D on C.id = D.c_id And so on. Yick.

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On 09/18/2012 07:32 AM, Tom Lane wrote: It's easier to understand why this is if you realize that SQL has a very clear model of a "pipeline" of query execution. I just wish they hadn't written it backwards! It'd be much less confusing were it formulated as something like: SELECT FROM thetable