[GENERAL] Junk date getting uploaded into date field

[bsreejithin]

We have a csv file which we upload into postgres DB. If there are some
errors, like a data mismatch with the database table columns, postgres
should raise and error and upload should fail.

What is happening now is that, in case we get some junk date in the upload
file, postgres does auto-correction and does not raise an error!

A wrong date like ,say, 33-Oct-2013 gets converted as 2-Nov-2013 when the
data is loaded into a date field. No error raised.Data gets uploaded!

How can I prevent this ? I don't want this junk data to get loaded.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Junk-date-getting-uploaded-into-date-field-tp5776969.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Junk date getting uploaded into date field

[John R Pierce]

On 11/5/2013 1:04 AM, bsreejithin wrote:

A wrong date like ,say, 33-Oct-2013 gets converted as 2-Nov-2013 when the
data is loaded into a date field. No error raised.Data gets uploaded!


um.   postgresql won't do that conversion

postgres=# select '33-Oct-2013'::date;
ERROR:  date/time field value out of range: "33-Oct-2013"
LINE 1: select '33-Oct-2013'::date;
   ^
.

how exactly are you inserting this CSV data into postgres ?

--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Keepalive doubt.

[Francisco Olarte]

Hello everybody:

I've found three server configuration parameters,
tcp_keepalives_{idle,interval,count} which control keepalive
behaviour. I know them, I know which my system defaults are, and I
know their default values, and I suppose in my systems ( linux ) they
correspond to setsockopt's of TCP_KEEP{INTVL,IDLE,CNT}.

But I've been unable to find any parameter to enable or disable
keepalives ( the equivalent of setsockopt with SO_KEEPALIVE ). I do
not know if this is because it's always turned on or if there is a
parameter for it which I've been unable to find.

So, before resorting to my usual last ditch solution ( Use the Source,
Luke ), I'll ask it here. Can anybody tell me if keepalives are
enabled by the server by default in Linux, and, if not, how do I
enable them?

Regards.
Francisco Olarte.


-- 
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] Junk date getting uploaded into date field

[Thomas Kellerer]

bsreejithin, 05.11.2013 10:04:
> We have a csv file which we upload into postgres DB. If there are some
> errors, like a data mismatch with the database table columns, postgres
> should raise and error and upload should fail.
> 
> What is happening now is that, in case we get some junk date in the upload
> file, postgres does auto-correction and does not raise an error!
> 
> A wrong date like ,say, 33-Oct-2013 gets converted as 2-Nov-2013 when the
> data is loaded into a date field. No error raised.Data gets uploaded!
> 
> How can I prevent this ? I don't want this junk data to get loaded.
> 

I'm pretty sure Postgres will not allow that. 

If I had to guess: you are using a Java program which uses SimpleDateFormat and 
the "lenient" parsing was not disabled.

Because that's exactly what happens with a SimpleDateFormat in it's default 
configuration.

http://docs.oracle.com/javase/6/docs/api/java/text/DateFormat.html#setLenient%28boolean%29






-- 
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] Keepalive doubt.

[Scott Marlowe]

On Tue, Nov 5, 2013 at 2:29 AM, Francisco Olarte  wrote:
> Hello everybody:
>
> I've found three server configuration parameters,
> tcp_keepalives_{idle,interval,count} which control keepalive
> behaviour. I know them, I know which my system defaults are, and I
> know their default values, and I suppose in my systems ( linux ) they
> correspond to setsockopt's of TCP_KEEP{INTVL,IDLE,CNT}.
>
> But I've been unable to find any parameter to enable or disable
> keepalives ( the equivalent of setsockopt with SO_KEEPALIVE ). I do
> not know if this is because it's always turned on or if there is a
> parameter for it which I've been unable to find.
>
> So, before resorting to my usual last ditch solution ( Use the Source,
> Luke ), I'll ask it here. Can anybody tell me if keepalives are
> enabled by the server by default in Linux, and, if not, how do I
> enable them?

They are enabled by default.


-- 
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] WITH RECURSIVE doesn't work properly for me

[Albe Laurenz]

Jing Fan wrote:
> I use following command to get a shortest-path query:
> 
> with recursive paths( src_id, dest_id, dist) as(
> select n1,n2,1
> from nodes
> union
> select src_id, dest_id, min(dist)
> from (  select paths.src_id as src_id, nodes.n2 as dest_id, 
> paths.dist+1 as dist
> from paths, nodes
> where paths.dest_id=nodes.n1
> and paths.src_id<>nodes.n2
> ) as Temp
> group by src_id, dest_id
> )
> select paths.src_id, paths.dest_id, min(dist)
> from paths
> group by 1,2;
> 
> It seems that this query goes into infinite loops and finally run out of disk 
> space. However, I testrf
> every iteration seperately and found that it will converge after 3-4 
> iterations. I wonder where is the
> problem. Could anyone help with it? The attatchment is the test data.

The attached test data suggest different table and column names,
but I assume that you mean "edge" when you write "nodes" and
that columns "n1" and "n2" are really "src_id" and "dest_id".

The endless loop occurs because there are loops in your
directed graph, but you only exclude circles where beginning
is equal to end.

To quote three lines from your attachment:
INSERT INTO edge (src_id, dest_id) VALUES (1739, 6236);
INSERT INTO edge (src_id, dest_id) VALUES (6236, 1739);
INSERT INTO edge (src_id, dest_id) VALUES (3384, 6236);

Your recursive WITH clause (CTE) will now happily produce:
 src_id | dest_id | dist
+-+--
   3384 |6236 |1
   3384 |1739 |2
   3384 |6236 |3
   3384 |1739 |4
   3384 |6236 |5
   3384 |1739 |6
   3384 |6236 |7
   3384 |1739 |8
   3384 |6236 |9
   3384 |1739 |   10
   3384 |6236 |   11
and so on to infinity, which is why you will eventually run
out of space.

The grouping (and any other processing in your main query)
takes place only after the CTE has been calculated, so while
your query would in theory return the desired result, it does
so only after calculating infinitely many intermediate rows.

One solution I can envision is to put a limit on the distance,
for example the total count of nodes.

Yours,
Laurenz Albe

-- 
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] Junk date getting uploaded into date field

[bsreejithin]

Not able to post the attached details as a comment in the reply box, so
attaching it as an image file : 
 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Junk-date-getting-uploaded-into-date-field-tp5776969p5776987.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Junk date getting uploaded into date field

[Thomas Kellerer]

bsreejithin, 05.11.2013 13:14:
> Not able to post the attached details as a comment in the reply box, so
> attaching it as an image file : 
>  

It would have much easier if you had simply used copy & paste to post a text 
version of that SQL.
Does your mail client not allow you to do that? 

But your test case is essentially this:

   select to_date('33-OCT-2013', 'dd-mon-')

which indeed returns 2013-11-02 (using 9.3.1)

I don't know if this is inteded or actually a bug - I can't find anything in 
the docs relating to that behaviour.

Thomas




-- 
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] Junk date getting uploaded into date field

[bsreejithin]

I am not using any mail client.I was directly trying to post the content I
attached.Got a connection reset by peer error on submit.Thought some issue
with the browser - so tried with firefox, chrome as well, in addition to IE
- got the same error there also. Any way, that's not the issue.

I am not sure why : select to_date('33-OCT-2013', 'dd-mon-') 

is returning 2013-11-02.

For cases like the issue I am facing, where we need to raise an error saying
the data is wrong, DB manipulating the data is not proper.

May be there could be some uses cases for such data manipulation. But then,
in that case, it would have been better to have a parameter which can switch
ON/OFF this behavior.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Junk-date-getting-uploaded-into-date-field-tp5776969p5776992.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Junk date getting uploaded into date field

[Edson Richter]

Em 05/11/2013 10:36, Thomas Kellerer escreveu:

bsreejithin, 05.11.2013 13:14:

Not able to post the attached details as a comment in the reply box, so
attaching it as an image file :


It would have much easier if you had simply used copy & paste to post a text 
version of that SQL.
Does your mail client not allow you to do that?

But your test case is essentially this:

select to_date('33-OCT-2013', 'dd-mon-')

which indeed returns 2013-11-02 (using 9.3.1)

I don't know if this is inteded or actually a bug - I can't find anything in 
the docs relating to that behaviour.

Thomas




I also don't know if this is intended or a bug, but for me, it seems to 
be right: 2013-11-02 is the 33 day counting from 2013-10-01.


Edson


--
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] Junk date getting uploaded into date field

[Albe Laurenz]

Thomas Kellerer wrote:
> bsreejithin, 05.11.2013 13:14:
>> Not able to post the attached details as a comment in the reply box, so
>> attaching it as an image file :
>> 
> 
> It would have much easier if you had simply used copy & paste to post a text 
> version of that SQL.
> Does your mail client not allow you to do that?
> 
> But your test case is essentially this:
> 
>select to_date('33-OCT-2013', 'dd-mon-')
> 
> which indeed returns 2013-11-02 (using 9.3.1)
> 
> I don't know if this is inteded or actually a bug - I can't find anything in 
> the docs relating to that
> behaviour.

There is a comment in utils/adt/formatting.c:

 * This function does very little error checking, e.g.
 * to_timestamp('20096040','MMDD') works

So at least this is not by accident.

On the other hand, I have always thought that these functions
are for Oracle compatibility, and sqlplus says:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT to_date('20096040','MMDD') FROM dual;
SELECT to_date('20096040','MMDD') FROM dual
   *
ERROR at line 1:
ORA-01843: not a valid month


I don't know if that should be fixed, but fixing it might break SQL
that deliberately uses the current behaviour.

Yours,
Laurenz Albe

-- 
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] Junk date getting uploaded into date field

[Reid Thompson]

On Tue, 2013-11-05 at 04:14 -0800, bsreejithin wrote:

> Not able to post the attached details as a comment in the reply box, so
> attaching it as an image file :
> 
>
>
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/Junk-date-getting-uploaded-into-date-field-tp5776969p5776987.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

to achieve what you want bdate needs to be defined as a date, not a
string.

test=# create table temptabl(bdate date);
CREATE TABLE
Time: 239.358 ms
test=# insert into temptabl values('33-OCT-2013');
ERROR:  date/time field value out of range: "33-OCT-2013"
LINE 1: insert into temptabl values('33-OCT-2013');
^
HINT:  Perhaps you need a different "datestyle" setting.
Time: 0.288 ms
test=#

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

[Jing Fan]

I have two group operations.
One is inside the CTE (   union
   select src_id, dest_id, min(dist) ),
another is outside the CTE.
Do you mean that even the grouping inside the CTE will be calculated only
after the CTE has been calculated?

Thank you very much:)

Best,
Jing




On Tue, Nov 5, 2013 at 5:04 AM, Albe Laurenz wrote:

> Jing Fan wrote:
> > I use following command to get a shortest-path query:
> >
> > with recursive paths( src_id, dest_id, dist) as(
> > select n1,n2,1
> > from nodes
> > union
> > select src_id, dest_id, min(dist)
> > from (  select paths.src_id as src_id, nodes.n2 as dest_id,
> paths.dist+1 as dist
> > from paths, nodes
> > where paths.dest_id=nodes.n1
> > and paths.src_id<>nodes.n2
> > ) as Temp
> > group by src_id, dest_id
> > )
> > select paths.src_id, paths.dest_id, min(dist)
> > from paths
> > group by 1,2;
> >
> > It seems that this query goes into infinite loops and finally run out of
> disk space. However, I testrf
> > every iteration seperately and found that it will converge after 3-4
> iterations. I wonder where is the
> > problem. Could anyone help with it? The attatchment is the test data.
>
> The attached test data suggest different table and column names,
> but I assume that you mean "edge" when you write "nodes" and
> that columns "n1" and "n2" are really "src_id" and "dest_id".
>
> The endless loop occurs because there are loops in your
> directed graph, but you only exclude circles where beginning
> is equal to end.
>
> To quote three lines from your attachment:
> INSERT INTO edge (src_id, dest_id) VALUES (1739, 6236);
> INSERT INTO edge (src_id, dest_id) VALUES (6236, 1739);
> INSERT INTO edge (src_id, dest_id) VALUES (3384, 6236);
>
> Your recursive WITH clause (CTE) will now happily produce:
>  src_id | dest_id | dist
> +-+--
>3384 |6236 |1
>3384 |1739 |2
>3384 |6236 |3
>3384 |1739 |4
>3384 |6236 |5
>3384 |1739 |6
>3384 |6236 |7
>3384 |1739 |8
>3384 |6236 |9
>3384 |1739 |   10
>3384 |6236 |   11
> and so on to infinity, which is why you will eventually run
> out of space.
>
> The grouping (and any other processing in your main query)
> takes place only after the CTE has been calculated, so while
> your query would in theory return the desired result, it does
> so only after calculating infinitely many intermediate rows.
>
> One solution I can envision is to put a limit on the distance,
> for example the total count of nodes.
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] Curious question about physical files to store database

[Patrick Dung]

On Monday, November 4, 2013 10:09 PM, Albe Laurenz  
wrote:
 
Patrick Dung wrote:

> As I have seen, some database created or pre-allocate large physical files on 
> the file system to as
> the backend of the database tablespace.
> 
> For Postgresql, I have observed that it created several files in the base and 
> global directory.
> 
> It may be by design, what is the pros and cons of this behavior?

You are a bit unspecific; are you talking about Oracle?

The disk layout is of course by design.

Oracle uses large container files and keeps its data in those.
As far as I know, this is to bypass file system functionality.
Oracle usually recommends direct I/O and bypasses file system
functionality (caching etc.) as much as possible.

I guess one reason for this is that, historically, file system
implementations incurred more overhead than they do now and had
all sorts of other problems with larger amounts of data.
These days, filesystems perform much better, so this is no longer
necessary, but Oracle is quite old software.

Another reason may be Oracle's quest to rule the world, and the
storage layer is part of that.  Lately, Oracle tries to get everybody
to use ASM, their storage layer, which completely bypasses
file system functionality.

PostgreSQL, on the other hand, does not have the resources or
intentions to write a better file system and actually uses
file system capabilities like caching to improve performance.

PostgreSQL keeps what Oracle calls segments as individual files
in a directory structure.

Yours,
Laurenz Albe


--

I have seen some databases product that allocate small number of large files.

Please correct me if I am wrong:

MSSQL (one file is the data and another file for the transaction log)
MySQL with InnoDB
Oracle
DB2

Thanks and regards,
Patrick

Re: [GENERAL] Junk date getting uploaded into date field

[Tom Lane]

Albe Laurenz  writes:
> There is a comment in utils/adt/formatting.c:

>  * This function does very little error checking, e.g.
>  * to_timestamp('20096040','MMDD') works

> So at least this is not by accident.

No, it isn't.  This is in fact the traditional behavior of Unix time
conversion utilities such as mktime(3).  The glibc man page gives the
specific example that "40 October" will be converted to "9 November"
rather than throwing an error.  The POSIX and C standards are not so
explicit, saying only that the inputs are not restricted to the normal
ranges (which I think would entitle an implementation to change 40
October to 31 October instead; but I've never heard of anyone doing
it that way).

regards, tom lane


-- 
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] Curious question about physical files to store database

[Albe Laurenz]

Patrick Dung wrote:
> I have seen some databases product that allocate small number of large files.
> 
> Please correct me if I am wrong:
> 
> MSSQL (one file is the data and another file for the transaction log)
> MySQL with InnoDB
> Oracle
> DB2

I don't know enough about DB2 and MSSQL, but you are correct
with regard to InnoDB and Oracle.

Yours,
Laurenz Albe

-- 
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] WITH RECURSIVE doesn't work properly for me

[Albe Laurenz]

Jing Fan wrote:
> I have two group operations.
> 
> One is inside the CTE (   union
>select src_id, dest_id, min(dist) ),
> another is outside the CTE.
> Do you mean that even the grouping inside the CTE will be calculated only 
> after the CTE has been
> calculated?

I mean the one outside the CTE.

The one inside does not do anything at all, you could omit it.

Yours,
Laurenz Albe

-- 
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] Junk date getting uploaded into date field

[Tom Lane]

bsreejithin  writes:
> I am not sure why : select to_date('33-OCT-2013', 'dd-mon-') 
> is returning 2013-11-02.
> For cases like the issue I am facing, where we need to raise an error saying
> the data is wrong, DB manipulating the data is not proper.

Then don't use to_date().  Just entering the string through the normal date
type input function will do what you want.  to_date() is meant for trying
to extract data from weird input formats.

regards, tom lane


-- 
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] WITH RECURSIVE doesn't work properly for me

[Jing Fan]

Why the one inside does not do anything? It won't be executed?

Best,
Jing


On Tue, Nov 5, 2013 at 8:52 AM, Albe Laurenz wrote:

> Jing Fan wrote:
> > I have two group operations.
> >
> > One is inside the CTE (   union
> >select src_id, dest_id, min(dist) ),
> > another is outside the CTE.
> > Do you mean that even the grouping inside the CTE will be calculated
> only after the CTE has been
> > calculated?
>
> I mean the one outside the CTE.
>
> The one inside does not do anything at all, you could omit it.
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] Junk date getting uploaded into date field

[Michael Nolan]

On 11/5/13, bsreejithin  wrote:
>
> I am not sure why : select to_date('33-OCT-2013', 'dd-mon-')
>
> is returning 2013-11-02.
>
> For cases like the issue I am facing, where we need to raise an error
> saying
> the data is wrong, DB manipulating the data is not proper.

Try using a cast to date instead:

select '33-oct-2013'::date throws an error.
--
Mike Nolan


-- 
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] Curious question about physical files to store database

[Edson Richter]

Em 05/11/2013 12:51, Albe Laurenz escreveu:

Patrick Dung wrote:

I have seen some databases product that allocate small number of large files.

Please correct me if I am wrong:

MSSQL (one file is the data and another file for the transaction log)
MySQL with InnoDB
Oracle
DB2

I don't know enough about DB2 and MSSQL, but you are correct
with regard to InnoDB and Oracle.

Yours,
Laurenz Albe

MSSQL can use several data files, but in general, you will have one for 
database data, and one for database log.
Advanced administrator would choose to split database among several 
files (specially in sparse disks). For instance, you would have one data 
file in one SCSI disk for data, and one data file in another SCSI disk 
for indexes, and a third SCSI disk for log. You can have several 
(hundreds? thousands? I can't remember) data files.


regards,

Edson Richter


--
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] WITH RECURSIVE doesn't work properly for me

[Albe Laurenz]

Jing Fan wrote:
> Why the one inside does not do anything? It won't be executed?

It is executed.

It might filter out the occasional row, but if you look at
the example I gave you, you'll see that it won't do anything
to keep it from recursing.

Yours,
Laurenz Albe

-- 
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] json datatype and table bloat?

[Merlin Moncure]

On Mon, Nov 4, 2013 at 8:31 PM, ajeli...@gmail.com  wrote:
 Along the lines of the equality operator; I have ran into issues trying
> to
 pivot a table/result set with a json type due what seemed to be no
 equality
 operator.
>>>
>>> For the curious, and also use-case considerations for development, would
>>> you
>>> be able to share what it is you are doing (and how) that combines full
>>> json
>>> documents with pivoting?
>>>
>>> Compound types holding source data for a pivot seems problematic since
>>> generally all the pivot components are single-valued and, for data, often
>>> numerical.
>
>>would also like to see this. json type has completely displaced
>>crosstab in my usage. I don't typically pivot json though: I pivot the
>>raw data then transform to json.  With limited exceptions I consider
>>storing json in actual table rows to be an anti-pattern (but it should
>>still work if you do it).
>
> I could not figure out what I was doing last month to reproduce this.  So  I
> did a small pivot poc, and it is erroring on the max function. So it is
> probably not the same issue. My guess is I tried the using the GREATEST
> function as a hail marry (which would not have worked) and got the following
> message; ERROR:  could not identify a comparison function for type json and
> then thought/hopped it was the same thing when reading the emails.
>
> CREATE TABLE bad_table_json(id int, detail_type text, details json);
> INSERT INTO bad_table_json values(1, 'a', '{"a":1}'::json);
> INSERT INTO bad_table_json values(1, 'b', '{"a":1}'::json);
> INSERT INTO bad_table_json values(1, 'c', '{"a":1}'::json);
>
> SELECT id
>   ,MAX(CASE WHEN detail_type = 'a' THEN details END) AS a
>   ,MAX(CASE WHEN detail_type = 'b' THEN details END) AS b
>   ,MAX(CASE WHEN detail_type = 'c' THEN details END) AS c
>   FROM bad_table_json
>  GROUP BY id

Aside: here's a way to do those type of things.  It's not faster
necessarily but seems cleaner to me.  This will bypass need for json
comparison.  IMMUTABLE plpgsql is generally the fastest way to
implement highly iterated trivial functions.

CREATE OR REPLACE FUNCTION PickInternal(State anyelement, WantValue
TEXT, PickValue TEXT, Value anyelement)
  RETURNS anyelement AS
$$
BEGIN
  RETURN CASE WHEN WantValue = PickValue THEN Value ELSE State END;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE AGGREGATE Pick(TEXT, TEXT, anyelement) (
  SFUNC=PickInternal,
  SType=anyelement);

SELECT id
  ,Pick('a', detail_type, details) AS a
  ,Pick('b', detail_type, details) AS b
  ,Pick('c', detail_type, details) AS c
  FROM bad_table_json
  GROUP BY id;

merlin


-- 
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] GSSAPI server side on Linux, SSPI client side on Windows

[Christian Ullrich]

* Stephen Frost wrote:


* Brian Crowell (br...@fluggo.com) wrote:

However, the eventual goal was to connect to this same server from a
.NET app running on Windows, and here I've run into a snag. The Npgsql
library does not support GSSAPI—it only supports SSPI, which is
nearly-but-not-enough-like the same thing to count in this situation,


Uhhh, why not?


Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does not 
recognize.


I tried to fix it using the reverse of they one-line fix that worked in 
both JDBC and libpq. There, the problem was that they only supported 
GSSAPI and had no clue about SSPI (except libpq on Windows). The fix was 
to basically declare GSSAPI and SSPI to be the same. It didn't work.


In Npgsql's case, the problem is the other way around -- it only knows 
SSPI. While making GSSAPI the same as SSPI should work in principle, 
there must be some difference somewhere.


--
Christian





--
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] GSSAPI server side on Linux, SSPI client side on Windows

[Stephen Frost]

* Christian Ullrich (ch...@chrullrich.net) wrote:
> I tried to fix it using the reverse of they one-line fix that worked
> in both JDBC and libpq. There, the problem was that they only
> supported GSSAPI and had no clue about SSPI (except libpq on
> Windows). The fix was to basically declare GSSAPI and SSPI to be the
> same. It didn't work.

If Npgsql does the same as libpq-on-Windows, it should all work just
fine..

> In Npgsql's case, the problem is the other way around -- it only
> knows SSPI. While making GSSAPI the same as SSPI should work in
> principle, there must be some difference somewhere.

Well, what happened after you hacked Npgsql?  It's possible there's a
bug or something it's doing different from libpq-on-Windows that is
causing a problem, or it might simply be an issue with configuration
(for example, you have to use POSTGRES as your server-side princ because
Windows is case-insensitive).

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows

[Christian Ullrich]

* Stephen Frost wrote:


* Christian Ullrich (ch...@chrullrich.net) wrote:



I tried to fix it using the reverse of they one-line fix that worked
in both JDBC and libpq. There, the problem was that they only
supported GSSAPI and had no clue about SSPI (except libpq on
Windows). The fix was to basically declare GSSAPI and SSPI to be the
same. It didn't work.


If Npgsql does the same as libpq-on-Windows, it should all work just
fine..


Hence my suspicion that it doesn't. I did not have the time to compare 
every function call yet.



In Npgsql's case, the problem is the other way around -- it only
knows SSPI. While making GSSAPI the same as SSPI should work in
principle, there must be some difference somewhere.


Well, what happened after you hacked Npgsql?  It's possible there's a


Nov  1 10:21:44 infra1 postgres[24864]: [7-1] FATAL:  GSSAPI 
authentication failed for user "chris"
Nov  1 10:25:27 infra1 postgres[25030]: [7-1] FATAL:  accepting GSS 
security context failed
Nov  1 10:25:27 infra1 postgres[25030]: [7-2] DETAIL:  An unsupported 
mechanism was requested: Unknown error
Nov  1 10:26:28 infra1 postgres[25079]: [7-1] FATAL:  accepting GSS 
security context failed
Nov  1 10:26:28 infra1 postgres[25079]: [7-2] DETAIL:  An unsupported 
mechanism was requested: Unknown error
Nov  1 10:30:41 infra1 postgres[25193]: [7-1] FATAL:  canceling 
authentication due to timeout
Nov  1 10:31:50 infra1 postgres[25277]: [7-1] FATAL:  accepting GSS 
security context failed
Nov  1 10:31:50 infra1 postgres[25277]: [7-2] DETAIL:  An unsupported 
mechanism was requested: Unknown error
Nov  1 10:39:31 infra1 postgres[25587]: [7-1] FATAL:  accepting GSS 
security context failed
Nov  1 10:39:31 infra1 postgres[25587]: [7-2] DETAIL:  Unspecified GSS 
failure.  Minor code may provide more information:
Nov  1 10:44:32 infra1 postgres[25778]: [7-1] FATAL:  accepting GSS 
security context failed
Nov  1 10:44:32 infra1 postgres[25778]: [7-2] DETAIL:  Unspecified GSS 
failure.  Minor code may provide more information:
Nov  1 10:44:56 infra1 postgres[25789]: [7-1] FATAL:  accepting GSS 
security context failed
Nov  1 10:44:56 infra1 postgres[25789]: [7-2] DETAIL:  Unspecified GSS 
failure.  Minor code may provide more information:


At some point during that I changed the principal that Npgsql gets its 
service ticket for from POSTGRES/ to POSTGRES/. 
There is a comment in the source that it does not work with the host 
name, with no more details, and I chose not to believe that. The result 
did nothing to prove me right, though. I think it was where the errors 
change from "accepting context failed" to "unspecified error", but I may 
be wrong.


The GSSAPI error messages are of the usual helpful kind, even including 
the colon that is followed by no detail.


I will spend more time on it once I have managed to keep my job this week.

--
Christian




--
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] WITH RECURSIVE doesn't work properly for me

[Jing Fan]

If the grouping inside CTE is executed, I don't think it would generate
result like

src_id | dest_id | dist
+-+--
   3384 |6236 |1
   3384 |1739 |2
   3384 |6236 |3
   3384 |1739 |4
   3384 |6236 |5
   3384 |1739 |6
   3384 |6236 |7
   3384 |1739 |8
   3384 |6236 |9
   3384 |1739 |   10
   3384 |6236 |   11

for we have min(dist),
so it should be like

src_id | dest_id | dist
+-+--
   3384 |6236 |1
   3384 |1739 |2

other values will be eliminated by min(). It actually generate no new
tuples and the iteration should stop.

Best,
Jing




On Tue, Nov 5, 2013 at 9:28 AM, Albe Laurenz wrote:

> Jing Fan wrote:
> > Why the one inside does not do anything? It won't be executed?
>
> It is executed.
>
> It might filter out the occasional row, but if you look at
> the example I gave you, you'll see that it won't do anything
> to keep it from recursing.
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows

[Francisco Figueiredo Jr.]

On Tue, Nov 5, 2013 at 2:35 PM, Christian Ullrich wrote:

> * Stephen Frost wrote:
>
>  * Brian Crowell (br...@fluggo.com) wrote:
>>
>>> However, the eventual goal was to connect to this same server from a
>>> .NET app running on Windows, and here I've run into a snag. The Npgsql
>>> library does not support GSSAPI—it only supports SSPI, which is
>>> nearly-but-not-enough-like the same thing to count in this situation,
>>>
>>
>> Uhhh, why not?
>>
>
> Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does not
> recognize.
>
> I tried to fix it using the reverse of they one-line fix that worked in
> both JDBC and libpq. There, the problem was that they only supported GSSAPI
> and had no clue about SSPI (except libpq on Windows). The fix was to
> basically declare GSSAPI and SSPI to be the same. It didn't work.
>
> In Npgsql's case, the problem is the other way around -- it only knows
> SSPI. While making GSSAPI the same as SSPI should work in principle, there
> must be some difference somewhere.
>
>

Did you make your changes in the NpgsqlState.cs file?


Also, while checking this part of the code, it seems Npgsql isn't handling
the AuthenticationGSS message. It is only handling
AuthenticationGSSContinue messages.

I think you could try adding the  AuthenticationGSS case to Npgsql and see
if it can authenticate correctly. You could add a second switch case below
the
case AuthenticationRequestType.AuthenticationSSPI and see if Npgsql can
also handle the GSS authentication correctly.

I didn't write SSPI support code so I'm sorry I'm not being able to provide
you with a much better explanation about this problem.

I hope it helps.






>  --
> Christian
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

Re: [GENERAL] Junk date getting uploaded into date field

[Steve Crawford]

On 11/05/2013 05:29 AM, Albe Laurenz wrote:

Thomas Kellerer wrote:

bsreejithin, 05.11.2013 13:14:

Not able to post the attached details as a comment in the reply box, so
attaching it as an image file :


It would have much easier if you had simply used copy & paste to post a text 
version of that SQL.
Does your mail client not allow you to do that?

But your test case is essentially this:

select to_date('33-OCT-2013', 'dd-mon-')

which indeed returns 2013-11-02 (using 9.3.1)

I don't know if this is inteded or actually a bug - I can't find anything in 
the docs relating to that
behaviour.

There is a comment in utils/adt/formatting.c:

  * This function does very little error checking, e.g.
  * to_timestamp('20096040','MMDD') works


I think the place for such warnings in addition to the source-code is in 
the documentation. This or similar issues with to_date have popped up on 
the lists a number of times.


Perhaps a "see warnings below" by the to_date description in table:
http://www.postgresql.org/docs/9.3/static/functions-formatting.html#FUNCTIONS-FORMATTING-TABLE

Then under usage notes something like:

The to_date and to_timestamp functions do minimal input error-checking 
and are intended for conversion of non-standard formats that cannot be 
handled by casting. These functions will attempt to convert illegal 
dates to the best of their ability, e.g. to_date('33-OCT-2013', 
'dd-mon-') will return 2013-11-02. Users of these functions are 
advised to perform whatever external error-checking they deem prudent.


Cheers,
Steve



--
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] GSSAPI server side on Linux, SSPI client side on Windows

[Christian Ullrich]

* Francisco Figueiredo Jr. wrote:


On Tue, Nov 5, 2013 at 2:35 PM, Christian Ullrich mailto:ch...@chrullrich.net>> wrote:

* Stephen Frost wrote:

* Brian Crowell (br...@fluggo.com ) wrote:

However, the eventual goal was to connect to this same
server from a
.NET app running on Windows, and here I've run into a snag.
The Npgsql
library does not support GSSAPI—it only supports SSPI, which is
nearly-but-not-enough-like the same thing to count in this
situation,


Uhhh, why not?


Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does
not recognize.

I tried to fix it using the reverse of they one-line fix that worked
in both JDBC and libpq. There, the problem was that they only
supported GSSAPI and had no clue about SSPI (except libpq on
Windows). The fix was to basically declare GSSAPI and SSPI to be the
same. It didn't work.

In Npgsql's case, the problem is the other way around -- it only
knows SSPI. While making GSSAPI the same as SSPI should work in
principle, there must be some difference somewhere.



Did you make your changes in the NpgsqlState.cs file?


Yes.


Also, while checking this part of the code, it seems Npgsql isn't
handling the AuthenticationGSS message. It is only handling
AuthenticationGSSContinue messages.

>
> I think you could try adding the  AuthenticationGSS case to Npgsql
> and see if it can authenticate correctly. You could add a second
> switch case below the case
> AuthenticationRequestType.AuthenticationSSPI and see if Npgsql can
> also handle the GSS authentication correctly.

That is exactly what I did.

I remember from my work on libpq that there is a slight difference in 
how it handles the two authentication types, but there it is just a flag 
for whether to treat the user name case-sensitively or not. Here, I 
control the case of the user part of the UPN, the claimed user name in 
the startup packet, and the role name in the database, and I know they 
are all identical. Therefore it should not matter for now whether Npgsql 
has similar logic already. To make GSSAPI support production-ready, I 
may have to add it, of course.


--
Christian




--
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] GSSAPI server side on Linux, SSPI client side on Windows

[Christian Ullrich]

* Christian Ullrich wrote:


Nov  1 10:31:50 infra1 postgres[25277]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:31:50 infra1 postgres[25277]: [7-2] DETAIL:  An unsupported
mechanism was requested: Unknown error
Nov  1 10:39:31 infra1 postgres[25587]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:39:31 infra1 postgres[25587]: [7-2] DETAIL:  Unspecified GSS
failure.  Minor code may provide more information:



name, with no more details, and I chose not to believe that. The result
did nothing to prove me right, though. I think it was where the errors
change from "accepting context failed" to "unspecified error", but I may
be wrong.


For "accepting context failed" read "unsupported mechanism", of course.

--
Christian




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Clone database using rsync?

[sparikh]

I need to clone production database to development  server ? What is the best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

Thanks in advance.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_start_backup('label',true) why do I need 2nd parameter?

[AI Rumman]

Hi all,

A few days back, I faced a problem where I *pg_start_backup('label') *was
hang in the server forever.
I stopped the process and then used *pg_start_backup('label',true) *and  it
worked.

Now I am trying to investigate why I need to use true as second parameter
and read the doc
"There is an optional second parameter of type boolean. If true, it
specifies executing pg_start_backup as quickly as possible. This forces an
immediate checkpoint which will cause a spike in I/O operations, slowing
any concurrently executing queries."

I tried to regenerate the scenario where  *pg_start_backup('label')
*hanged and
I failed.
Any idea, how can I regenerate that issue to investigate.

I am using Postgresql 9.2.

Thanks.

Re: [GENERAL] Clone database using rsync?

[Jeff Janes]

On Tue, Nov 5, 2013 at 3:11 PM, sparikh  wrote:

> I need to clone production database to development  server ? What is the
> best
> and simplest way to achieve that? Both my production and development
> postgres versions are same. Will copy over data folder using rsync work?
>

How are you currently backing up your production database?  I usually
piggy-back off of that method to set up the clone. It kills two birds with
one stone, you both get your clone for dev, and you exercise your disaster
recovery procedure.

Cheers,

Jeff

Re: [GENERAL] Clone database using rsync?

[John R Pierce]

On 11/5/2013 3:11 PM, sparikh wrote:

I need to clone production database to development  server ? What is the best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?


if the database isn't gigenormous, on the new ssytem, I use the 
following, while logged on as postgres...


1. manually create any roles and tablespaces the database requires
2. manually create the new database owned by the proper role
3. pg_dump -h oldhost -d dbname -Fc | pg_restore -d newdb

copying the data folder only works if you either stop the postgres 
server, or invoke pg_start_backup(), or use pg_basebackup [1]



[1] http://www.postgresql.org/docs/current/static/app-pgbasebackup.html



--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] Clone database using rsync?

[matt]

Copying the data folder should work as long as you stop the postgres
service on the production server before starting the copy and don't start
it up again until the copy finishes.  pg_dump and pg_restore (look them up
in the online docs) will get the job done without you having to take the
production server offline.

If you go with the folder copy and your installation has postgresql.conf,
pg_hba.conf and so on in your data folder, you'll probably want to edit
them after the copy - more logging, different security etc.

Matt


> I need to clone production database to development  server ? What is the
> best
> and simplest way to achieve that? Both my production and development
> postgres versions are same. Will copy over data folder using rsync work?
>
> Thanks in advance.
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
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] Clone database using rsync?

[Adrian Klaver]

On 11/05/2013 03:41 PM, m...@byrney.com wrote:

Copying the data folder should work as long as you stop the postgres
service on the production server before starting the copy and don't start
it up again until the copy finishes.  pg_dump and pg_restore (look them up
in the online docs) will get the job done without you having to take the
production server offline.

If you go with the folder copy and your installation has postgresql.conf,
pg_hba.conf and so on in your data folder, you'll probably want to edit
them after the copy - more logging, different security etc.


OP did not say what version they where on but pg_basebackup could be an 
option, no shutdown necessary either:


http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html



Matt



I need to clone production database to development  server ? What is the
best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

Thanks in advance.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general








--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Clone database using rsync?

[sparikh]

Thanks Jeff for your quick response.

I inherited this system and they had cron job which uses pg_dump for back
up. I recently used to rsync to bring back my hot standby when it was out of
sync and offline for few days because of space issue. That is when the
thought that I might be able to use rsync to clone database.

If I understood your reply correctly, you are currently using rsync for both
clone and backup.  Is that correct?

Thanks Again.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141p5777150.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] pg_start_backup('label',true) why do I need 2nd parameter?

[David Johnston]

rummandba wrote
> A few days back, I faced a problem where I *pg_start_backup('label') *was
> hang in the server forever.

so, the server is still hung?  can you define what you mean by "hung" - what
did and did not work?


> I stopped the process and then used *pg_start_backup('label',true) *and 
> it
> worked.

Ok, so not forever :)  How long then?  Any guess you give will be more
accurate than "forever".

So when you told it to "start now" it did but when you told it "start
whenever you are ready" is said "ok, I'll get back to ya." ...


> This forces an
> immediate checkpoint which will cause a spike in I/O operations, slowing
> any concurrently executing queries."

>From the same documentation (section 24.3.3):


> By default, pg_start_backup can take a long time to finish. This is
> because it performs a checkpoint, and the I/O required for the checkpoint
> will be spread out over a significant period of time, by default half your
> inter-checkpoint interval (see the configuration parameter
> checkpoint_completion_target). This is usually what you want, because it
> minimizes the impact on query processing. If you want to start the backup
> as soon as possible, use: [the true parameter]

So assuming defaults you would be expected to wait about 2.5 minutes:

5 minutes default checkpoint_timeout
times .5 default checkpoint_completion_target

[note, I am confused by the wording above if there should be another .50
multiplier]

That is significantly less than forever but it may be more time that you
gave it before cancelling the process...and that assumes you are using
defaults.

Without knowing the setting for "checkpoint_timeout" (and completion target,
but you are less likely to have changed that) it is impossible to know
whether your finite wait was longer than could be expected.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-start-backup-label-true-why-do-I-need-2nd-parameter-tp5777145p5777153.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] pg_start_backup('label',true) why do I need 2nd parameter?

[Bill Moran]

On Tue, 5 Nov 2013 15:30:19 -0800 AI Rumman  wrote:

> Hi all,
> 
> A few days back, I faced a problem where I *pg_start_backup('label') *was
> hang in the server forever.
> I stopped the process and then used *pg_start_backup('label',true) *and  it
> worked.
> 
> Now I am trying to investigate why I need to use true as second parameter
> and read the doc
> "There is an optional second parameter of type boolean. If true, it
> specifies executing pg_start_backup as quickly as possible. This forces an
> immediate checkpoint which will cause a spike in I/O operations, slowing
> any concurrently executing queries."

To add to what David said in his email ... essentially with the second
parameter at false (which is the same as omitting it) you are telling
the system to not interfere with other concurrently running processes
and take as much time as necessary to prep the system without interfering.
How long that takes is a factor of other settings (as David mentioned) and
also depedent on what other transactions may be running.

With the second parameter at true, you're telling Postgres to execute the
start backup as fast as possible and if other queries are temporarily
slow or unresponsive while it's getting there, that's OK.

As far as exactly replicating the scenerio, you would need to know what
else was running on the server at the time to exactly replicate it.

> 
> I tried to regenerate the scenario where  *pg_start_backup('label')
> *hanged and
> I failed.
> Any idea, how can I regenerate that issue to investigate.
> 
> I am using Postgresql 9.2.
> 
> Thanks.


-- 
Bill Moran 


-- 
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] pg_start_backup('label',true) why do I need 2nd parameter?

[David Johnston]

Bill Moran wrote
> How long that takes is a factor of other settings (as David mentioned) and
> also depedent on what other transactions may be running.

While I am inclined to believe this is true the documentation is unclear
that "other transactions" have any bearing on the delay.  All the
documentation says is that the checkpoint I/O will be spread out over time. 
Period.  I could see where if there is no pending checkpoint I/O to perform
that it will return immediately but does having 100MB of I/O to perform,
versus 10MB of I/O to perform, cause the delay to increase 9-fold up to a
maximum of whatever timeframe is configured?  

The wording implies that the delay, say 2.5 minutes by default (if I am
reading that right), will be used regardless so the system will incur a rate
of 4MB/min of checkpoint I/O in the better case and 40MB/min of checkpoint
I/O for the worse case.

The other possibility is that there is a floor of 10MB/min of checkpoint I/O
so the first example only takes 1 minute to return (not 2.5) while the
second uses the entire allotted time and also must increase the I/O rate.

I'm not sure the precise algorithm needs to be documented but "can take a
long time to finish" seems to be taking to the other extreme.  Assuming one
of the two examples above is correct including such an example in the
documentation (i.e., comparing 0MB, 10MB, and 100MB of pending checkpoint
I/O) is a thought.  Also, is there a way to query how much checkpoint I/O
currently is outstanding?  If so, and the value is meaningful to this
determination, a cross-reference to said information would be useful.

Also, assuming the algorithm is fairly constant having it documented at this
level, with maybe an example query, would allow people to calculate roughly
the amount of time the "false" call will take to return.

And yes, I am getting a little carried away here...

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-start-backup-label-true-why-do-I-need-2nd-parameter-tp5777145p5777173.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general