Re: [GENERAL] Potential bug with pg_notify

> On Feb 13, 2017, at 1:56 PM, Adrian Klaver wrote: > > On 02/13/2017 09:04 AM, François Beaulieu wrote: >> >>> On Feb 13, 2017, at 11:45 AM, Adrian Klaver >>> wrote: >>> >

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

Thanks guys, here's the information you requested: psql:postgres@cipafilter = show work_mem; work_mem ── 10MB (1 row) psql:postgres@cipafilter = select version(); version

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

Thanks Jeff, No triggers or foreign key constrains: psql:postgres@cipafilter = \d+ titles Table "public.titles" Column │ Type│Modifiers │ Storage │ Stats target │ Description

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

psql:postgres@cipafilter = EXPLAIN (ANALYZE, BUFFERS) select titleid from titles WHERE NOT EXISTS ( SELECT 1 FROM log_raw WHERE log_raw.titleid = titles.titleid ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

13.02.2017 23:58, Rader, David: How about using pg_isready? https://www.postgresql.org/docs/current/static/app-pg-isready.html But it doesn't actually communicate with the server AFAIK, just checks if a connection could be established? Maybe I should have been more specific. What I need is

Re: [GENERAL] Postgres

On Sat, Feb 11, 2017 at 12:48 AM, prakash ramakrishnan wrote: > Hi, > >Am Prakash from Chennai and am working in postgres edb 9.5 I need > your help for pgpool and pgbouncer configuration steps and please keep in > touch if I get any error. why don't you ask some

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 12:43 PM, David Hinkle wrote: > Thanks Jeff, > > No triggers or foreign key constrains: > > psql:postgres@cipafilter = \d+ titles > Table "public.titles" > Column │ Type│

[GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within some continued (valid) session, but

Re: [GENERAL] Potential bug with pg_notify

On 02/13/2017 11:50 AM, François Beaulieu wrote: > >> On Feb 13, 2017, at 1:56 PM, Adrian Klaver wrote: >> >> On 02/13/2017 09:04 AM, François Beaulieu wrote: >>> On Feb 13, 2017, at 11:45 AM, Adrian Klaver wrote: >>

[GENERAL] xmlelement AND timestamps.

I just migrated from 9.2.4 to 9.6.1 and had several user created functions fail. Recreating the failure with "SELECT xmlelement(name foo, 'infinity'::timestamp) ERROR: timestamp out of range DETAIL: XML does not support infinite timestamp values. I don't find anything in the documentation

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

I managed to get this version to finish: psql:postgres@cipafilter = explain (ANALYZE, BUFFERS) select count(*) from (select titleid from log_raw group by titleid) as a; QUERY PLAN

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 11:53 AM, David Hinkle wrote: > Thanks guys, here's the information you requested: > > psql:postgres@cipafilter = show work_mem; > work_mem > ── > 10MB > (1 row) > OK, new theory then. Do you have triggers on or foreign key constraints

Re: [GENERAL] Postgres

On 2/10/2017 10:48 PM, prakash ramakrishnan wrote: Am Prakash from Chennai and am working in postgres edb 9.5 I need your help for pgpool and pgbouncer configuration steps and please keep in touch if I get any error. I can't imagine any scenario where you'd use pgpool and pgbouncer

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

I manually updated the pg_statistics data by literally set it to an appropriate amount, and the planner picked a new plan and the new plan worked. Any idea what I should do about this? Is manually updating these values my best bet? psql:daemon@cipafilter = update pg_statistic set stadistinct =

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

How about using pg_isready? https://www.postgresql.org/docs/current/static/app-pg-isready.html -- David Rader dav...@openscg.com On Sun, Feb 12, 2017 at 12:23 PM, Nikolai Zhubr wrote: > Hello all, > > In order to locate the problem more precisely, I'd like to prepare a

[GENERAL] Documentation inconsistency (at least to me)

I wonder why regexp_split_to_array() is listed under "String functions and operators" [1] but string_to_array() is listed under "Array functions and operators" [2] I find that a bit inconsistent - I would expect to find both in the same chapter. I would suggest to put both into "String

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

On Mon, Feb 13, 2017 at 5:10 PM, Thomas Kellerer wrote: > Nikolai Zhubr schrieb am 13.02.2017 um 23:03: > >> Maybe I should have been more specific. >> What I need is debugging/profiling pure communication side of server >> operation, implying huge lots of requests and

Re: [GENERAL] xmlelement AND timestamps.

On Mon, Feb 13, 2017 at 7:10 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > XML itself is textual and we don't have any internal support for DTD or > Schema as it is so I'm not sure what material benefit we gain by > restraining ourselves here. > ​This apparently isn't true - the

Re: [GENERAL] Auto-Rollback option

Yes, I'm talking about pgAdmin III - sorry... I think that auto-commit is on on default but auto-rollback is off. But I'll check if you say so. And I know I can check the box next to Enable Auto ROLLBACK but I'm trying to avoid it and enable auto rollback not by a manual way. -- View this

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

On Tue, Feb 14, 2017 at 7:03 AM, Nikolai Zhubr wrote: > 13.02.2017 23:58, Rader, David: >> >> How about using pg_isready? >> https://www.postgresql.org/docs/current/static/app-pg-isready.html > > But it doesn't actually communicate with the server AFAIK, just checks if a >

Re: [GENERAL] xmlelement AND timestamps.

On Mon, Feb 13, 2017 at 6:36 PM, Adrian Klaver wrote: > On 02/13/2017 02:56 PM, Lynn Dobbs wrote: > >> I just migrated from 9.2.4 to 9.6.1 and had several user created >> functions fail. >> >> Recreating the failure with "SELECT xmlelement(name foo, >>

Re: [GENERAL] xmlelement AND timestamps.

On 02/13/2017 02:56 PM, Lynn Dobbs wrote: I just migrated from 9.2.4 to 9.6.1 and had several user created functions fail. Recreating the failure with "SELECT xmlelement(name foo, 'infinity'::timestamp) ERROR: timestamp out of range DETAIL: XML does not support infinite timestamp values. I

Re: [GENERAL] xmlelement AND timestamps.

Lynn Dobbs writes: > I just migrated from 9.2.4 to 9.6.1 and had several user created > functions fail. > Recreating the failure with "SELECT xmlelement(name foo, > 'infinity'::timestamp) > ERROR: timestamp out of range > DETAIL: XML does not support infinite

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 1:10, Thomas Kellerer: Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within

[GENERAL] PostgreSQL corruption

Hello All, I am working with a client who is facing issues with database corruption after a physical hard power off (the machines are at remote sites, this could be a power outage or user error). They have an environment made up of many of the following consumer grade stand alone machines: -

Re: [GENERAL] PostgreSQL corruption

On Mon, Feb 13, 2017 at 9:41 PM, Scott Marlowe wrote: > On Mon, Feb 13, 2017 at 9:21 PM, James Sewell > wrote: >> >> Hello All, >> >> I am working with a client who is facing issues with database corruption >> after a physical hard power off

Re: [GENERAL] PostgreSQL corruption

On Mon, Feb 13, 2017 at 9:21 PM, James Sewell wrote: > > Hello All, > > I am working with a client who is facing issues with database corruption > after a physical hard power off (the machines are at remote sites, this could > be a power outage or user error). > >

[GENERAL] Write from Postgres to SQL Server

I find myself in an environment wanting to move to Postgres, having a variety of smaller Postgres databases, but the business currently revolves around a “God” SQL Server database. I’m looking for general advice about making the two work together. More specifically, interoperating between the

Re: [GENERAL] Auto-Rollback option

On 02/13/2017 02:10 PM, mpomykacz wrote: Yes, I'm talking about pgAdmin III - sorry... I think that auto-commit is on on default but auto-rollback is off. But I'll check if you say so. Did you look here: https://www.pgadmin.org/docs/1.22/options-query_tool.html It seems checking it here

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

On 02/13/2017 06:04 AM, Stephen Frost wrote: Adrian, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: I am following this up to the point of not understanding what exactly changed between 9.5 and 9.6. Namely 9.5 does include the default ACL's in the dump output and 9.6 does not. Quite a

Re: [GENERAL] Auto-Rollback option

Take a look at ON_ERROR_STOP variable. \set ON_ERROR_STOP 1 On 13/02/2017 15:55, Małgorzata Hubert wrote: Hi, is there any way to set Auto-Rollback : ON, automaticly during instalation process or using query (maybe something like set autocommit = 'on')? We need it to automaticly close the

Re: [GENERAL] Auto-Rollback option

On 02/13/2017 05:55 AM, Małgorzata Hubert wrote: Hi, is there any way to set Auto-Rollback : ON, automaticly during instalation process or using query (maybe something like set autocommit = 'on')? We need it to automaticly close the transaction if an error occures during implementing patches.

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Adrian, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > I am following this up to the point of not understanding what > exactly changed between 9.5 and 9.6. Namely 9.5 does include the > default ACL's in the dump output and 9.6 does not. Quite a bit in pg_dump changed, but the relevant bit

[GENERAL] Auto-Rollback option

Hi, is there any way to set Auto-Rollback : ON, automaticly during instalation process or using query (maybe something like set autocommit = 'on')? We need it to automaticly close the transaction if an error occures during implementing patches. Thanks in advanced for the answear. Best regards,

Re: [GENERAL] Potential bug with pg_notify

On 02/10/2017 02:54 PM, François Beaulieu wrote: Hi all, I’m trying to feed a worker process on another server using pg_notify in a trigger. I’m running pgsql 9.4 and hitting some behaviour that I’m hoping is just a bug that can be solved with an upgrade, but I’m not finding any references

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Frank, * Frank van Vugt (ftm.van.v...@foxi.nl) wrote: > Op zaterdag 11 februari 2017 15:28:55 schreef Tom Lane: > > I'm inclined to argue that it was a mistake to include any non-pinned > > objects in pg_init_privs. > > > We might need to fix pg_dump too, but I think these entries in > >

Re: [GENERAL] Auto-Rollback option

On Mon, Feb 13, 2017 at 02:55:03PM +0100, Małgorzata Hubert wrote: > is there any way to set Auto-Rollback : ON, automaticly during instalation > process or using query (maybe something like set autocommit = 'on')? > We need it to automaticly close the transaction if an error occures during >

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I'm not seeing a very simple answer for this, unfortunately. > > I'm inclined to argue that it was a mistake to include any non-pinned > objects in pg_init_privs. The reason initdb leaves some objects

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Hi Stephen, Op maandag 13 februari 2017 09:10:42 schreef Stephen Frost: > We should be able to get it addressed shortly. Great, 'as always', I'd like to add! Thanks for the great work, people. This cannot be stated too often... > For your specific case Thanks for the additional info,

Re: [GENERAL] Potential bug with pg_notify

> On Feb 13, 2017, at 11:45 AM, Adrian Klaver wrote: > > On 02/13/2017 07:59 AM, François Beaulieu wrote: >> >>> On Feb 13, 2017, at 10:28 AM, Adrian Klaver >>> wrote: >>> >>> On 02/10/2017 02:54 PM, François Beaulieu wrote:

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

On 02/13/2017 07:52 AM, Stephen Frost wrote: Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 02/13/2017 06:04 AM, Stephen Frost wrote: * Adrian Klaver (adrian.kla...@aklaver.com) wrote: I am following this up to the point of not understanding what exactly changed between 9.5

Re: [GENERAL] Recorded PostgreSQL at 10TB and Beyond

On Mon, 13 Feb 2017 09:52:05 + Seref Arikan wrote: > Many thanks for this. > +1 -- Bien à vous, Vincent Veyron https://libremen.com Logiciels de gestion, libres -- Sent via pgsql-general mailing list

Re: [GENERAL] Potential bug with pg_notify

> On Feb 13, 2017, at 10:28 AM, Adrian Klaver wrote: > > On 02/10/2017 02:54 PM, François Beaulieu wrote: >> >> Hi all, >> >> I’m trying to feed a worker process on another server using pg_notify in a >> trigger. I’m running pgsql 9.4 and hitting some behaviour

Re: [GENERAL] Potential bug with pg_notify

On 02/13/2017 07:59 AM, François Beaulieu wrote: On Feb 13, 2017, at 10:28 AM, Adrian Klaver wrote: On 02/10/2017 02:54 PM, François Beaulieu wrote: Hi all, I’m trying to feed a worker process on another server using pg_notify in a trigger. I’m running pgsql

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Frank, * Frank van Vugt (ftm.van.v...@foxi.nl) wrote: > Well, I didn't run into this issue with any of my db's that 'nicely' use > tables in various schema's, it was actually the one 'older' db with > everything > in the public schema that brought it up, so maybe keeping one of those around >

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 02/13/2017 06:04 AM, Stephen Frost wrote: > >* Adrian Klaver (adrian.kla...@aklaver.com) wrote: > >>I am following this up to the point of not understanding what > >>exactly changed between 9.5 and 9.6. Namely 9.5 does include the

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Hi Tom/Stephen/Adrian, Op zaterdag 11 februari 2017 15:28:55 schreef Tom Lane: > I'm inclined to argue that it was a mistake to include any non-pinned > objects in pg_init_privs. > We might need to fix pg_dump too, but I think these entries in > pg_init_privs should simply not be there. Thanks

[GENERAL] Recorded PostgreSQL at 10TB and Beyond

Hi all; There at least one request for the recorded talk. It is available at https://www.youtube.com/watch?v=8mKpfutwD0U The version to be delivered at Moscow will be slightly different in focus (but with the same slides) so no if you see one you may still get something out of coming to the

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

Hi 2017-02-13 18:40 GMT+01:00 David Hinkle : > I'm having trouble with purges related to a large table. The delete > query consumes ram until postgres crashes due to OOM. I have a very > large table called log_raw. There are half a dozen related tables, > such as

[GENERAL] Bad planning data resulting in OOM killing of postgres

I'm having trouble with purges related to a large table. The delete query consumes ram until postgres crashes due to OOM. I have a very large table called log_raw. There are half a dozen related tables, such as 'urls' and 'titles'. log_raw.urlid = urls.urlid and urls contains the text of

Re: [GENERAL] Auto-Rollback option

On 2/13/2017 7:15 AM, mpomykacz wrote: So my problem is like this: I start the transaction with BEGIN TRANSACTION; Then I have for example some INSERTs to DB and at the end COMMIT; and END TRANSACTION; COMMIT ends the transaction. In PostgreSQL, END TRANSACTION is redundant, equivalent to

Re: [GENERAL] Auto-Rollback option

On 02/13/2017 09:59 AM, John R Pierce wrote: On 2/13/2017 7:15 AM, mpomykacz wrote: So my problem is like this: I start the transaction with BEGIN TRANSACTION; Then I have for example some INSERTs to DB and at the end COMMIT; and END TRANSACTION; COMMIT ends the transaction. In PostgreSQL,

Re: [GENERAL] Auto-Rollback option

On 02/13/2017 09:59 AM, John R Pierce wrote: > On 2/13/2017 7:15 AM, mpomykacz wrote: >> So my problem is like this: >> >> I start the transaction with BEGIN TRANSACTION; >> Then I have for example some INSERTs to DB >> and at the end COMMIT; and END TRANSACTION; > > COMMIT ends the transaction.

Re: [GENERAL] Auto-Rollback option

Il 13/02/2017 18:59, John R Pierce ha scritto: option? query editor window? what software are you talking about? I'm using 1.22.1 version. 1.22.1 version? PostgreSQL versions currently supported are 9.2.x to 9.6.x I think he's talking about pgAdmin III Cheers Moreno --

Re: [GENERAL] Auto-Rollback option

So my problem is like this: I start the transaction with BEGIN TRANSACTION; Then I have for example some INSERTs to DB and at the end COMMIT; and END TRANSACTION; But if one of this INSERTs causes error, the transaction will stop (but it is still open and next patch is implemented within the

Re: [GENERAL] Auto-Rollback option

On Mon, Feb 13, 2017 at 1:10 PM, Adrian Klaver wrote: > On 02/13/2017 09:59 AM, John R Pierce wrote: > >> On 2/13/2017 7:15 AM, mpomykacz wrote: >> >>> So my problem is like this: >>> >>> I start the transaction with BEGIN TRANSACTION; >>> Then I have for example some

Re: [GENERAL] Recorded PostgreSQL at 10TB and Beyond

Many thanks for this. On Mon, Feb 13, 2017 at 9:36 AM, Chris Travers wrote: > Hi all; > > There at least one request for the recorded talk. It is available at > https://www.youtube.com/watch?v=8mKpfutwD0U > > The version to be delivered at Moscow will be slightly

Re: [GENERAL] Fwd: Query parameter types not recognized

Hi Rob, Thanks for your answer. The query is just an example I made to illustrate the problem. In the database I'm working with, duedate is a timestamp without timezone column, which can contain null values. The parameter is supposed to be of type DATE. From Java, I'm sending a Date object (which

[GENERAL] Potential bug with pg_notify

Hi all, I’m trying to feed a worker process on another server using pg_notify in a trigger. I’m running pgsql 9.4 and hitting some behaviour that I’m hoping is just a bug that can be solved with an upgrade, but I’m not finding any references to it being a known bug and the uptime on my

Re: [GENERAL] Fwd: Query parameter types not recognized

Hmmm... I didn't know PostgreSQL had a facility for query logging and debugging of parameters to a logfile. Thought I had to execute a describe or something like that. Thanks, I'll try it to see what's happening! 2017-02-10 16:57 GMT-05:00 Adrian Klaver : > On

Re: [GENERAL] Fwd: Query parameter types not recognized

Hi Arjen, I already tried that too. In that case, the error changes to `org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp without time zone and interval cannot be matched`. I listed all the operators available for dates, and `+` and `-` take a date and an integer to return a

Re: [GENERAL] Fwd: Query parameter types not recognized

Hi, The parameter defaultDueDate is a java.sql.Date object, an actual Date. When I run the query with the value in it, it works: ```sql db=> select COALESCE(duedate, date '2017-02-01' + 1) from invoices order by duedate desc; coalesce - 2017-02-02 00:00:00 2017-02-02

[GENERAL] Postgres

Hi, Am Prakash from Chennai and am working in postgres edb 9.5 I need your help for pgpool and pgbouncer configuration steps and please keep in touch if I get any error. Thanks Prakash.r 9551559623

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 9:40 AM, David Hinkle wrote: > I'm having trouble with purges related to a large table. The delete > query consumes ram until postgres crashes due to OOM. I have a very > large table called log_raw. There are half a dozen related tables, > such

Re: [GENERAL] Auto-Rollback option

On Mon, Feb 13, 2017 at 1:19 PM, Moreno Andreo wrote: > Il 13/02/2017 18:59, John R Pierce ha scritto: > >> option? query editor window? what software are you talking about? >> >> I'm using 1.22.1 version. >>> >> >> 1.22.1 version? PostgreSQL versions

Re: [GENERAL] Potential bug with pg_notify

On 02/13/2017 09:04 AM, François Beaulieu wrote: On Feb 13, 2017, at 11:45 AM, Adrian Klaver wrote: | 3) Are the first row and the second row in the same partition? Doubtful, the problem