Re: [GENERAL] Critical failure of standby

Hello, I double posted this (posted once from an unregistered email and assumed it would be junked). I'm continuing all discussion on the other thread now. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

Xtra Coder wrote: > May you have the link to 'DO'-discussion to take a look on it? I was trying > to google for something like that, but word 'DO' is too generic to bring > useful results :( Probably this is one: https://www.postgresql.org/message-id/51b624c6@2ndquadrant.com -- Álvaro

Re: [GENERAL] Critical failure of standby

(from other thread) - 9.5.3 - Redhat 7.2 on VMWare - Single PostgreSQL instance one each machine - Every machine in DR became corrupt, so interestingly this must have been sent to the two cascading nodes via WAL before the crash on the hub DR node - No OS logs indicating

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

May you have the link to 'DO'-discussion to take a look on it? I was trying to google for something like that, but word 'DO' is too generic to bring useful results :( In my particular case I'm more interested in an easy way to create complex SELECTs that require usage of variables in the one-time

Re: [GENERAL] How to parse xml containing optional elements

Hi, apt-get upgrade postgresql-9.1 returns Reading package lists... Done Building dependency tree Reading state information... Done You might want to run 'apt-get -f install' to correct these. The following packages have unmet dependencies: openssl : Depends: libssl1.0.0 (>= 1.0.1e-2+deb7u5)

Re: [GENERAL] How to parse xml containing optional elements

Hi! Thank you. How to upgrade in Debian Squeeze ? A plain "apt-get upgrade postgresql-9.1" does not work? It might help to enable the postgresql.org APT repository. For instructions, see here: https://www.postgresql.org/download/linux/debian/ apt-get upgrade postgresql-9.1 returns Reading

Re: [GENERAL] How to parse xml containing optional elements

Andrus, Psotgres 9.1 run isn Debian Squeeze which is unsupported. How to upgrade in Debian Squeeze ? A plain "apt-get upgrade postgresql-9.1" does not work? It might help to enable the postgresql.org APT repository. For instructions, see here:

Re: [GENERAL] Critical failure of standby

James Sewell wrote: > 2016-08-12 04:43:53 GMT [23614]: [5-1] user=,db=,client= (0:0)LOG: > consistent recovery state reached at 3/8811DFF0 > 2016-08-12 04:43:53 GMT [23614]: [6-1] user=,db=,client= (0:XX000)FATAL: > invalid memory alloc request size 3445219328 > 2016-08-12 04:43:53 GMT

Re: [GENERAL] Corrupted Data ?

On 08/12/2016 11:51 AM, Ioana Danes wrote: On Fri, Aug 12, 2016 at 2:50 PM, Adrian Klaver > wrote: On 08/12/2016 08:30 AM, Ioana Danes wrote: The db3 database is on a different machine from all the other

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 2:50 PM, Adrian Klaver wrote: > On 08/12/2016 08:30 AM, Ioana Danes wrote: > >> >> >> > >> >> The db3 database is on a different machine from all the other >> databases you set up, correct? >> >> Yes, they are all different vms first 3

Re: [GENERAL] Corrupted Data ?

On 08/12/2016 08:30 AM, Ioana Danes wrote: The db3 database is on a different machine from all the other databases you set up, correct? Yes, they are all different vms first 3 dbs are on the same cluster but drdb is a remote machine, This rattled around in my head and got less

Re: [GENERAL] Critical failure of standby

On Fri, Aug 12, 2016 at 1:39 AM, James Sewell wrote: > Hello, > > We recently experienced a critical failure when failing to a DR > environment. > > This is in the following environment: > > >- 3 x PostgreSQL machines in Prod in a sync replication cluster >- 3

[GENERAL] Critical failure of standby

Hello, We recently experienced a critical failure when failing to a DR environment. This is in the following environment: - 3 x PostgreSQL machines in Prod in a sync replication cluster - 3 x PostgreSQL machines in DR, with a single machine async and the other two cascading from the

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Thank you Craig, this has worked in my custom function too: BEGIN PERFORM check_positions(in_uid, in_gid, in_tiles); CREATE TEMP TABLE _words ON COMMIT DROP AS SELECT out_word AS word, max(out_score) AS score FROM

Re: [GENERAL] Avoiding re-inventing a wheel

On Fri, 12 Aug 2016, Adrian Klaver wrote: Or are the various levels tightly coupled and you are looking to start from scratch? Adrian, I want to start from scratch. Yet, I think that I found a solution that will work better than that that. There's a tool called XRMS that appears to have

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 12:22 PM, Adrian Klaver wrote: > On 08/12/2016 08:49 AM, Ioana Danes wrote: > >> >> >> On Fri, Aug 12, 2016 at 11:44 AM, Ioana Danes > > wrote: >> >> >> > kvm >> >> Sorry I should add more

Re: [GENERAL] Avoiding re-inventing a wheel

On 08/12/2016 09:42 AM, Rich Shepard wrote: On Fri, 12 Aug 2016, Adrian Klaver wrote: FYI regular(classic) wxPython does not support Python 3. If you are looking forward you want: https://www.wxpython.org/Phoenix/docs/html/index.html Adrian, Yes, I'm aware of this and decided that this

Re: [GENERAL] Avoiding re-inventing a wheel

On Fri, 12 Aug 2016, Adrian Klaver wrote: FYI regular(classic) wxPython does not support Python 3. If you are looking forward you want: https://www.wxpython.org/Phoenix/docs/html/index.html Adrian, Yes, I'm aware of this and decided that this new application should be written using

Re: [GENERAL] Error at dynamic generated copy...

On 08/12/2016 09:34 AM, Edmundo Robles wrote: don't worry. I still working in this copy, how can i do to insert newlines to generate the copy dynamically and avoid the syntax error. I think there is a bigger problem. From one of attempts: ERROR: cannot COPY to/from client in

Re: [GENERAL] Error at dynamic generated copy...

don't worry. I still working in this copy, how can i do to insert newlines to generate the copy dynamically and avoid the syntax error. On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver wrote: > On 08/12/2016 08:15 AM, Edmundo Robles wrote: > >> Adrian, your'e

Re: [GENERAL] Avoiding re-inventing a wheel

On 08/12/2016 09:18 AM, Rich Shepard wrote: I need to expand my client relation management/sales tracking application from its existing python3/Qt5/SQLite3 version to a more capable one built on a postgres backend. (For the record, I've been using postgres and sqlite on linux for almost 20

Re: [GENERAL] Corrupted Data ?

On 08/12/2016 08:49 AM, Ioana Danes wrote: On Fri, Aug 12, 2016 at 11:44 AM, Ioana Danes > wrote: kvm Sorry I should add more info kernel 4.7 and the filesystem is xfs vs ext3/ext4 Not following the above. Do you mean the physical

[GENERAL] Avoiding re-inventing a wheel

I need to expand my client relation management/sales tracking application from its existing python3/Qt5/SQLite3 version to a more capable one built on a postgres backend. (For the record, I've been using postgres and sqlite on linux for almost 20 years now so I'm comfortable with both.)

Re: [GENERAL] Error at dynamic generated copy...

On 08/12/2016 08:15 AM, Edmundo Robles wrote: Adrian, your'e right, the real problem is the slow insert, I have many devices reporting to the server and saving their state each minute so there is a moment where i reach the limit of connections and the monitor device send a exception

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 11:44 AM, Ioana Danes wrote: > > > On Fri, Aug 12, 2016 at 11:34 AM, Adrian Klaver > wrote: > >> On 08/12/2016 08:30 AM, Ioana Danes wrote: >> >>> >>> >>> On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver >>>

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 11:34 AM, Adrian Klaver wrote: > On 08/12/2016 08:30 AM, Ioana Danes wrote: > >> >> >> On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver >> > wrote: >> >> On 08/12/2016 08:10 AM,

Re: [GENERAL] pgbasebackup is failing after truncate

On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar wrote: > HI > > At present, I have some requirement to truncate the data base to reclaim > disk space and at the same time I need to take basebackup. > > Seems, truncate is successfully reclaimed the space but

Re: [GENERAL] Corrupted Data ?

On 08/12/2016 08:30 AM, Ioana Danes wrote: On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver > wrote: On 08/12/2016 08:10 AM, Ioana Danes wrote: On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte

Re: [GENERAL] Error at dynamic generated copy...

On 08/12/2016 08:15 AM, Edmundo Robles wrote: Adrian, your'e right, the real problem is the slow insert, I have many devices reporting to the server and saving their state each minute so there is a moment where i reach the limit of connections and the monitor device send a exception

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver wrote: > On 08/12/2016 08:10 AM, Ioana Danes wrote: > >> >> >> On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte >> > wrote: >> >> CCing to the list... >> >>

Re: [GENERAL] Corrupted Data ?

On 08/12/2016 08:10 AM, Ioana Danes wrote: On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte > wrote: CCing to the list... Thanks On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes

Re: [GENERAL] Error at dynamic generated copy...

Adrian, your'e right, the real problem is the slow insert, I have many devices reporting to the server and saving their state each minute so there is a moment where i reach the limit of connections and the monitor device send a exception and crash. The table grows a lot, current

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte wrote: > CCing to the list... > > Thanks > On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes wrote: > >> given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it > >> repeats sporadically,

Re: [GENERAL] Error at dynamic generated copy...

On 08/12/2016 07:11 AM, Edmundo Robles wrote: Hi! I hope you could help me... I tried to generate the next copy instruction in a function: copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin DELIMITER as '|' ; 12060157|John|Doe|33 \. ** The commands to generate the copy

Re: [GENERAL] Corrupted Data ?

CCing to the list... On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes wrote: >> given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it >> repeats sporadically, have you ruled out ( by having page checksums or >> other mechanism ) a potential disk read/write error ?

Re: [GENERAL] Error at dynamic generated copy...

On 08/12/2016 07:11 AM, Edmundo Robles wrote: Hi! I hope you could help me... I tried to generate the next copy instruction in a function: copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin DELIMITER as '|' ; 12060157|John|Doe|33 \. ** The commands to generate the copy

[GENERAL] Error at dynamic generated copy...

Hi! I hope you could help me... I tried to generate the next copy instruction in a function: copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin DELIMITER as '|' ; 12060157|John|Doe|33 \. ** The commands to generate the copy are: CREATE OR REPLACE FUNCTION

Re: [GENERAL] pgbasebackup is failing after truncate

On 08/10/2016 11:06 PM, Yelai, Ramkumar wrote: HI At present, I have some requirement to truncate the data base to reclaim disk space and at the same time I need to take basebackup. To be clear you ran TRUNCATE on tables within the database, correct? If not what was the actual command you

Re: [GENERAL] Standby crash

On 08/11/2016 11:28 PM, James Sewell wrote: Hello, We recently experienced a critical failure when failing to a DR environment. This is in the following environment: What is the version of Postgres used? What is the OS and version used? * 3 x PostgreSQL machines in Prod in a sync

Re: [GENERAL] Corrupted Data ?

Hi Melvin, On Fri, Aug 12, 2016 at 9:36 AM, Melvin Davidson wrote: > > > On Fri, Aug 12, 2016 at 9:09 AM, Ioana Danes wrote: > >> Hello Everyone, >> >> I have new information on this case. I also open a post for Bucardo >> because I am still not

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 3:09 PM, Ioana Danes wrote: > drawid | 318220 ... > drawid | 318216 > Here are the facts I know: > > August 10 @ 11:10 > - The record was created on db1 and replicated to db2 and db3 > August 11 @ 2:30 >

Re: [GENERAL] Corrupted Data ?

On Fri, Aug 12, 2016 at 9:09 AM, Ioana Danes wrote: > Hello Everyone, > > I have new information on this case. I also open a post for Bucardo > because I am still not sure what triggers this problem. > > The problem happened again on the same table but on another field.

Re: [GENERAL] Corrupted Data ?

Hello Everyone, I have new information on this case. I also open a post for Bucardo because I am still not sure what triggers this problem. The problem happened again on the same table but on another field. Few days ago I started a fourth database called drdb that is a PITR slave from db3.

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 11:32 AM, Karsten Hilbert wrote: > På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers < > chris.trav...@gmail.com[chris.trav...@gmail.com]>: > > > My preference is stored procedures plus service locators > > I know your work on the former

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 11:24 AM, Daevor The Devoted wrote: > > > On Fri, Aug 12, 2016 at 10:58 AM, Andreas Joseph Krogh > wrote: > > Could you elaborate on this method? Or direct me to docs describing it in > more detail? I'm quite interested in this

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

On 12 August 2016 at 18:43, Alexander Farber wrote: > Thank you, I have rewritten it into: > > BEGIN > PERFORM check_positions(in_uid, in_gid, in_tiles); > > CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT > DROP; > > INSERT

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Thank you, I have rewritten it into: BEGIN PERFORM check_positions(in_uid, in_gid, in_tiles); CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT DROP; INSERT INTO _words SELECT out_word AS word, max(out_score) AS

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers :   > My preference is stored procedures plus service locators I know your work on the former with respect to the financial app you are working on. Would you care to elaborate a little

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

På fredag 12. august 2016 kl. 11:24:16, skrev Daevor The Devoted < doll...@gmail.com >:   [snip] If you don't like your domain-model to be very close to your physical DB-model, there's nothing preventing you from having a persistence-model, using the ORM, and map that

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 10:58 AM, Andreas Joseph Krogh wrote: > På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers < > chris.trav...@gmail.com>: > > > > On Fri, Aug 12, 2016 at 9:59 AM, Andreas Joseph Krogh > wrote: >> >> På fredag 12. august

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

På fredag 12. august 2016 kl. 11:07:08, skrev Chris Travers < chris.trav...@gmail.com >: [snip] My preference is stored procedures plus service locators, to be honest.  It enables a degree of loose coupling and even dynamic discovery that ORMs are generally not

Re: [GENERAL] How to parse xml containing optional elements

Hi! I couldn't really believe this so I just installed a VM and a 9.1 postgresql just to test this for you. It seems you hit a bug in PostgreSQL prior to 9.1.15: https://www.postgresql.org/docs/9.1/static/release-9-1-15.html "Fix namespace handling in xpath() (Ali Akbar) Previously, the xml

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

2016-08-12 11:00 GMT+02:00 Alexander Farber : > Francisco, thanks, but - > > On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte > wrote: > >> >> https://www.postgresql.org/docs/9.5/static/plpgsql-statement >>

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 10:58 AM, Andreas Joseph Krogh wrote: > På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers < > chris.trav...@gmail.com>: > > > > >> >> Of course you *can* use them well. I remember talking about this with >> one author or a major ORM and he

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Alexander: On Fri, Aug 12, 2016 at 11:00 AM, Alexander Farber wrote: > but the custom function I am trying to call (from another function) does not > return one row, but several rows, which I'd like to store into a temp table: This I know, I wasn't trying to solve

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Francisco, thanks, but - On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte wrote: > > https://www.postgresql.org/docs/9.5/static/plpgsql- > statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW > > but the custom function I am trying to call (from another function) does not

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers < chris.trav...@gmail.com >:     On Fri, Aug 12, 2016 at 9:59 AM, Andreas Joseph Krogh > wrote: På fredag 12. august 2016 kl. 05:27:42, skrev Chris Travers

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

On Fri, Aug 12, 2016 at 10:41 AM, Alexander Farber wrote: > why does this syntax fail in 9.5.3 please? Maybe because... > $func$ LANGUAGE plpgsql; ... you are writing pspgsql. > The doc https://www.postgresql.org/docs/9.5/static/sql-selectinto.html just

[GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Good morning, why does this syntax fail in 9.5.3 please? I am trying to call 2 custom functions from a third one with: CREATE OR REPLACE FUNCTION play_game( IN in_uid integer, IN in_gid integer, IN in_tiles jsonb, OUT out_gid integer) RETURNS integer AS

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 9:59 AM, Andreas Joseph Krogh wrote: > På fredag 12. august 2016 kl. 05:27:42, skrev Chris Travers < > chris.trav...@gmail.com>: > > > > On Thu, Aug 11, 2016 at 10:20 PM, Andreas Joseph Krogh > wrote: >> >> På torsdag 11. august

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

På fredag 12. august 2016 kl. 05:27:42, skrev Chris Travers < chris.trav...@gmail.com >:     On Thu, Aug 11, 2016 at 10:20 PM, Andreas Joseph Krogh > wrote: På torsdag 11. august 2016 kl. 19:13:08, skrev support-tiger

[GENERAL] Standby crash

Hello, We recently experienced a critical failure when failing to a DR environment. This is in the following environment: - 3 x PostgreSQL machines in Prod in a sync replication cluster - 3 x PostgreSQL machines in DR, with a single machine async and the other two cascading from the