Re: [GENERAL] backup.old

Honestly I don't know. It was either pg_dump or a recovery script, that would have included a call to pg_start_backup. It does have the start_backup looking format. START WAL LOCATION: F3/128 (file 004500F30001) CHECKPOINT LOCATION: F3/160 BACKUP METHOD: pg_start_backup BA

Re: [GENERAL] backup.old

On 10/6/2015 8:28 PM, Steve Pribyl wrote: What do I need to do to recover a database server that has a backup.old file in the data_directory. I have see references to a database being stopped during a backup and/or a second backup running and moving the old file aside, but it was not clear to

[GENERAL] backup.old

Good Evening, What do I need to do to recover a database server that has a backup.old file in the data_directory. I have see references to a database being stopped during a backup and/or a second backup running and moving the old file aside, but it was not clear to me what needs to be done do

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

Oleksii Kliukin writes: > This should work, but I'm interested in finding out why the original > statement behaves the way I’ve described. plpgsql's SELECT INTO is only capable of storing a single result row, so it only executes the statement far enough to obtain one row, and then stops (as tho

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

On 10/06/2015 02:00 PM, Oleksii Kliukin wrote: On 06 Oct 2015, at 22:50, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 10/06/2015 01:48 PM, Oleksii Kliukin wrote: On 06 Oct 2015, at 22:40, Adrian Klaver mailto:adrian.kla...@aklaver.com> > wrote

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

> On 06 Oct 2015, at 22:50, Adrian Klaver wrote: > > On 10/06/2015 01:48 PM, Oleksii Kliukin wrote: >> >>> On 06 Oct 2015, at 22:40, Adrian Klaver >> >>> >> >>> wrote: >>> >>> On 10/06/2015

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

On 10/06/2015 01:48 PM, Oleksii Kliukin wrote: On 06 Oct 2015, at 22:40, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 10/06/2015 01:13 PM, Oleksii Kliukin wrote: Basically, if we invoke the first example, the foo table with have only 1 row and not 10, as supplied by the generat

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

> On 06 Oct 2015, at 22:40, Adrian Klaver wrote: > > On 10/06/2015 01:13 PM, Oleksii Kliukin wrote: >> >> Basically, if we invoke the first example, the foo table with have only >> 1 row and not 10, as supplied by the generate_series. >> However, when ORDER BY is attached to the query, or aggre

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

On 10/06/2015 01:13 PM, Oleksii Kliukin wrote: Hello, I have an issue with a function that is called as a part of the SELECT INTO target list in pl/pgSQL. I'd like to illustrate it with a simple example: DO $$ DECLARE l_id integer; BEGIN SELECT test(id) INTO l_id FROM gene

[GENERAL] dubious optimization of the function in SELECT INTO target list

Hello, I have an issue with a function that is called as a part of the SELECT INTO target list in pl/pgSQL. I'd like to illustrate it with a simple example: DO $$ DECLARE l_id integer; BEGIN SELECT test(id) INTO l_id FROM generate_series(1,10) t(id); END; $$ LANGUAGE plpgs

Re: [GENERAL] Best practices for aggregate table design

On Tue, Oct 6, 2015 at 2:53 PM, droberts wrote: > > month | city_id | state_id | total_calls_inbound | total_calls_outbound | > total_calls_inbound_encr | total_calls_outbound_encr | > > getting a bit hairy but the alternative seems like it would start growing > too quickly in rows and more I/O f

Re: [GENERAL] Best practices for aggregate table design

Okay, so is it safe to say I should use loosely use these guidelines when deciding whether to model an attribute as a dimension (type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ? If you know the number of values for a dimension are fixed (e.g. boolean), then creating a measure

Re: [GENERAL] Best practices for aggregate table design

On Tue, Oct 6, 2015 at 2:34 PM, John R Pierce wrote: > On 10/6/2015 11:24 AM, droberts wrote: > >> OR a dimension 'type' with values outbound/inbound and a single measure >> column 'total' ? >> > > that smells a bit too much like an "EAV" (entity-attribute-value) which is > considered an antipatt

Re: [GENERAL] Best practices for aggregate table design

On 10/6/2015 11:24 AM, droberts wrote: OR a dimension 'type' with values outbound/inbound and a single measure column 'total' ? that smells a bit too much like an "EAV" (entity-attribute-value) which is considered an antipattern in relational circles -- john r pierce, recycling bits in santa

Re: [GENERAL] Best practices for aggregate table design

Thanks for your response. One more follow-up question. Is there a best practice when to create a measure that includes a property/dimension? Let me give an example, say in my example where I have outbound and inbound calls. Is is best to have measures: -total_inbound -total_outbound OR

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

On 10/6/2015 2:38 AM, Steve Pritchard wrote: I am porting several stored procedures from Oracle to Postgres. In the Oracle code, if an exception is thrown within a stored procedure, the exception is caught and details are written to a database table using an autonomous transaction (as the main

Re: [GENERAL] Serialization errors despite KEY SHARE/NO KEY UPDATE

On 10/05/2015 11:17 PM, Kevin Grittner wrote: Jim Nasby wrote: On 10/2/15 11:44 AM, Olivier Dony wrote: On 10/02/2015 12:28 AM, Jim Nasby wrote: On 9/29/15 9:47 AM, Olivier Dony wrote: -- Setup tables CREATE TABLE users ( id serial PRIMARY KEY, name varchar,

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 10:38 AM, Basques, Bob (CI-StPaul) < bob.basq...@ci.stpaul.mn.us> wrote: > Just to throw in an idea. > > I almost exclusively use PERL for this type of thing. A bunch of examples > out on the web using DBI, and the main aspects are portable across many > databases, not just

Re: [GENERAL] Best practices for aggregate table design

On Tue, Oct 6, 2015 at 11:59 AM, droberts wrote: > Hi, > I'm trying to construct an agg table to capture phone call data and group > by > state, city and time but also want just general measures by month. I'm > thinking to have this: > > month | city_id | state_id | total_calls_inbound | total_ca

[GENERAL] Best practices for aggregate table design

Hi, I'm trying to construct an agg table to capture phone call data and group by state, city and time but also want just general measures by month. I'm thinking to have this: month | city_id | state_id | total_calls_inbound | total_calls_outbound | total_calls 2015-01 12 2 54 2 56 2015-01 10

Re: [GENERAL] [pgsql-es-ayuda] No funciona WITH con mas de 2 sentencias DML

Hellmuth Vargas escribió: > Hola Lista > > Estaba realizando un cargue de un archivo Excel con información de clientes > bancarios con tarjeta para un call center poblando un modelo maestro, > detalle y tabla de llamadas telefónicas. En un principio se implemento por > medio de una herramienta de

Re: [GENERAL] Processing data from table using awk.

Just to throw in an idea. I almost exclusively use PERL for this type of thing. A bunch of examples out on the web using DBI, and the main aspects are portable across many databases, not just POSTGRES. Just my two cents. AWK would work too, I’ve used it myself, and got very complicated with i

[GENERAL] No funciona WITH con mas de 2 sentencias DML

Hola Lista Estaba realizando un cargue de un archivo Excel con información de clientes bancarios con tarjeta para un call center poblando un modelo maestro, detalle y tabla de llamadas telefónicas. En un principio se implemento por medio de una herramienta de ETL con los controles que ofrece la h

Re: [GENERAL] BDR Rejoin of failed node, hangs.

That was it thanks. Steve Pribyl Sr. Systems Engineer steve.pri...@akunacapital.com Desk: 312-994-4646 From: Craig Ringer Sent: Tuesday, October 6, 2015 12:35 AM To: Steve Pribyl Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] BDR Rejoin of faile

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 9:25 AM, Reid Thompson wrote: > On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote: > > > I'm wanting to do some reporting on data which I have an a PostgreSQL > table. > > For lack of anything better, I've decided to see if I can do it in GNU > awk. > > > perhaps... note

Re: [GENERAL] Processing data from table using awk.

On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote: > I'm wanting to do some reporting on data which I have an a PostgreSQL table. > For lack of anything better, I've decided to see if I can do it in GNU awk. perhaps... note the 4th extension... https://www.gnu.org/software/gawk/manual/html_n

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 10:15 AM, Melvin Davidson wrote: > Your best bet is something like > > #!/bin/bash > > get_data () > { > QRY=$(psql $HOST $PORT $USER $DBNAME <<_QUERY_ > > \o your_output_file > SELECT col1, col2, , coln > FROM your_table > WHERE ; > > _QUERY_ > > ) > } > > awk your_

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 10:04 AM, John McKown wrote: > I'm wanting to do some reporting on data which I have an a PostgreSQL > table. For lack of anything better, I've decided to see if I can do it in > GNU awk. OK, using Perl with DBI might be a better idea, I'll grant you > that. Or maybe Python

Re: [GENERAL] Processing data from table using awk.

Your best bet is something like #!/bin/bash get_data () { QRY=$(psql $HOST $PORT $USER $DBNAME <<_QUERY_ \o your_output_file SELECT col1, col2, , coln FROM your_table WHERE ; _QUERY_ ) } awk your_table On Tue, Oct 6, 2015 at 10:04 AM, John McKown wrote: > I'm wanting to do some rep

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

Scott Mead writes: >> On Oct 6, 2015, at 05:38, Steve Pritchard wrote: >> [ how to fake an autonomous transaction? ] > It's hacky, and, I haven't tried it in a few years. Setup a foreign table > that resides in the same database. When you write to the foreign table, it > will be using a 'loop

[GENERAL] Processing data from table using awk.

I'm wanting to do some reporting on data which I have an a PostgreSQL table. For lack of anything better, I've decided to see if I can do it in GNU awk. OK, using Perl with DBI might be a better idea, I'll grant you that. Or maybe Python or Ruby (which I don't know). But out of shear cussedness, I'

Re: [GENERAL] How to reduce pg_dump backup time

On Tue, Oct 6, 2015 at 8:54 AM, Adrian Klaver wrote: > On 10/05/2015 09:52 PM, Sachin Srivastava wrote: > >> Dear Team, >> >> I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. >> My database size is 680 GB and it take 7 hour for completion the pg_dump >> backup. >> >> I want

Re: [GENERAL] How to reduce pg_dump backup time

On 10/05/2015 09:52 PM, Sachin Srivastava wrote: Dear Team, I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My database size is 680 GB and it take 7 hour for completion the pg_dump backup. I want that my pg_dump backup should be fast and take less time. In PostgresQL 9.

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

On 10/06/2015 02:38 AM, Steve Pritchard wrote: I am porting several stored procedures from Oracle to Postgres. In the Oracle code, if an exception is thrown within a stored procedure, the exception is caught and details are written to a database table using an autonomous transaction (as the main

Re: [GENERAL] md5(large_object_id)

Karsten Hilbert wrote: > On Mon, Oct 05, 2015 at 03:27:26PM +, Kevin Grittner wrote: > > > Karsten Hilbert wrote: > > > > > I am dealing with radiology studies aka DICOM data) one would > > > want an md5 function which streams in parts of a large object > > > piece by piece using md

Re: [GENERAL] How to reduce pg_dump backup time

> On Oct 6, 2015, at 00:52, Sachin Srivastava wrote: > > Dear Team, > > > > I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My > database size is 680 GB and it take 7 hour for completion the pg_dump backup. > > > > I want that my pg_dump backup should be fast and

Re: [GENERAL] [Q] Serializable

David G. Johnston wrote: > On Thu, Sep 24, 2015 at 12:15 PM, Ladislav Lenart wrote: >> Suppose I have two (or more) concurrent DB transactions: >> * Each runs in SERIALIZABLE. >> * Each updates (insert / update / delete) different rows in the >> same table. >> >> Can I get serializable failure

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

> On Oct 6, 2015, at 05:38, Steve Pritchard wrote: > > I am porting several stored procedures from Oracle to Postgres. In the Oracle > code, if an exception is thrown within a stored procedure, the exception is > caught and details are written to a database table using an autonomous > transac

[GENERAL] Recording exceptions within function (autonomous transactions?)

I am porting several stored procedures from Oracle to Postgres. In the Oracle code, if an exception is thrown within a stored procedure, the exception is caught and details are written to a database table using an autonomous transaction (as the main transaction is rolled back). As far as I can see

Re: [GENERAL] Selecting pairs of numbers

Charles Clavadetscher wrote: >> aklaver@test=> create table pr_test(x int, y int); >> >> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >> (3,2) order by x,y; >> x | y >> ---+--- >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 > > +1