Re: [GENERAL] Fwd: [BUGS] BUG #14850: Implement optinal additinal parameter for 'justify' date/time function

[Brian Dunavant]

A 'month' is an abstract measurement of time.  Sometimes it's 29 days, 30,
or 31.   You cannot say "I have 30 days, how many months is that?" because
the answer is "it depends".

 -  gives you an interval in days.   In your example, you took
Jan 31 2016 and added "1 month".  Postgres says "I know feb 2016 is 29
days" and did it automatically for you.   When you then subtracted Jan 31
2016, you now have "29 days".   Postgres can no longer say "that is 1
month" because you cannot go that direction.

You are also using extract(month from X) incorrectly if you want the number
of months between any time period.   That will only return a value between
0 and 11.

It will also be difficult because you are starting from a random day in the
month, making it hard to really know what you mean.  Postgres' age()
function may be able to help you with 'months'.

flpg=# select age( '2016-02-01'::timestamp, '2016-01-01'::timestamp );
  age
---
 1 mon

flpg=# select age( '2016-02-29'::timestamp, '2016-01-31'::timestamp );
   age
-
 29 days
(1 row)

flpg=# select age( '2016-03-01'::timestamp, '2016-01-31'::timestamp );
 age
-
 1 mon 1 day





On Thu, Oct 12, 2017 at 4:00 AM, KES  wrote:

>
>
>  Пересылаемое сообщение
> 11.10.2017, 17:12, "Pavel Stehule" :
>
> Hi
>
> 2017-10-11 12:35 GMT+02:00 :
>
> The following bug has been logged on the website:
>
> Bug reference:  14850
> Logged by:  Eugen Konkov
> Email address:  kes-...@yandex.ru
> PostgreSQL version: 10.0
> Operating system:   Linux mint 18: Linux work 4.4.0-57-generic #78-Ubu
> Description:
>
> Hi. I try to do next math:
>
> select extract( month from justify_days( timestamp '2016-01-31' +interval
> '1
> month' -timestamp '2016-01-31') );
>  date_part
> ---
>  0
> (1 row)
>
> I expect `1` but get `0`. But here everything is right:
>
> >Adjust interval so 30-day time periods are represented as months
>
> https://www.postgresql.org/docs/9.6/static/functions-datetime.html
>
> But with ability to setup justify date the math will be more sharp.
>
> Please implement next feature:
>
> select extract( month from justify_days( timestamp '2016-01-31' +interval
> '1
> month' -timestamp '2016-01-31'), timestamp '2016-01-31' );
>  date_part
> ---
>  1
> (1 row)
>
> This is useful when I try to calculate how much month are left between
> service start and end dates.
>
>
> This is not the bug, so pgsql-hackers, pgsql-general are better places for
> this discussion
>
> I am thinking so your request has sense, and should be registered in ToDo
> list https://wiki.postgresql.org/wiki/Todo
>
> You can try to connect people from PostgreSQL Pro company for
> implementation.
>
> Regards
>
> Pavel
>
>
> Thank you.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>
>
>  Конец пересылаемого сообщения 
>

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

[Brian Dunavant]

"FOR UPDATE" is part of "SELECT" not part of "UPDATE".

You can select the rows "for update" which will lock those rows.  You
can then loop over the the results of the 'select' to do the rest of
your logic.

Be careful doing this if other things are also updating these rows.
With SKIP LOCKED you can skip over rows that should have been selected
but were not because another process was updating data that was
unrelated.   Without SKIP LOCKED you risk deadlock if you are
selecting multiple rows.



On Mon, Jul 10, 2017 at 3:22 PM, Alexander Farber
 wrote:
> I have tried:
>
>   FOR _gid, _loser, _winner IN
> UPDATE  words_games
> SET finished = CURRENT_TIMESTAMP
> WHERE   finished IS NULL
> AND played1 IS NOT NULL
> AND played2 IS NOT NULL
> AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'
> OR   played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
> RETURNING
> gid,
> CASE WHEN played1 < played2 THEN player1 ELSE player2 END,
> CASE WHEN played1 < played2 THEN player2 ELSE player1 END
> FOR UPDATE SKIP LOCKED
>   LOOP
> ...
>   END LOOP;
>
> but this fails with:
>
> ERROR:  syntax error at or near "FOR"
>
> I have also described my problem at SO:
>
>
> https://stackoverflow.com/questions/45015368/how-to-handle-simultaneous-for-in-update-returning-loops
>
> Thank you
> Alex


-- 
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] Select from tableA - if not exists then tableB

[Brian Dunavant]

On Tue, May 9, 2017 at 6:00 PM, Patrick B  wrote:
> SELECT
> split_part(n1.path::text, '/'::text, 18)::integer AS id,
> split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
> lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
> '-1000-1000-3000-6000'::uuid AS guid,
> n1.bytes AS byte_count,
> n1.last_modified AS last_modified
>   FROM tablea n1
>   JOIN tableb s2 ON s2.path = n1.path
>
> Where tablec is the new one. AS you can see, there is no reference for the
> new tablec on that query, so I need to:
>
> - Get the data from the new table,
> - if it is not in there, then go to old table (query above).


I'm assuming tablec is supposed to replace tablea.

Being a view makes it trickier.  You can still do it with:

SELECT
  split_part(n1.path::text, '/'::text, 18)::integer AS id,
  split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
  lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
  '-1000-1000-3000-6000'::uuid AS guid,
  n1.bytes AS byte_count,
  n1.last_modified AS last_modified
FROM (
 select DISTINCT ON (id) [columns] from (
select [columns/pads], 1 as tableorder from tablec
union all
select [columns/pads], 2 as tableorder from tablea
 ) t
 ORDER BY id, tableorder
   ) n1
  JOIN tableb s2 ON s2.path = n1.path;

This will cause it to prefer the data in tablec, but use any id's in
tablea that aren't in tablec .

This may be very slow, as i'm not sure if predicate pushdown would
happen here, so this may cause full table scans of both tablea and
tablec possibly making performance bad if those are large tables.   It
should do what you are asking for though.


-- 
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] Select from tableA - if not exists then tableB

[Brian Dunavant]

>From what you're saying about migrating, I'm assuming the new table
has additional columns or something.  If you can map the difference,
then you could use CTE's to select from the first table, and if
nothing is there, then pull from the second table and pad it with
nulls so they "match".  This should work fine in 9.1.

For example:

db=# create table old ( id integer );
CREATE TABLE
db=# create table new ( id integer, newcol text );
CREATE TABLE
db=# insert into old (id) values (1), (2);
INSERT 0 2
db=# insert into new (id, newcol) values (1, 'a');
INSERT 0 1

New table:

db=# with new_check as (
db(#   select id, newcol from new where id = 1
db(# )
db-# select id, null::text as newcol from old where id = 1
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
  1 | a
(1 row)

Old table:

db=# with new_check as (
db(#   select id, newcol from new where id = 2
db(# )
db-# select id, null::text as newcol from old where id = 2
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
  2 |
(1 row)

Neither:

db=# with new_check as (
db(#   select id, newcol from new where id = 3
db(# )
db-# select id, null::text as newcol from old where id = 3
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
(0 rows)





On Mon, May 8, 2017 at 5:56 PM, Patrick B  wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.
>
> Thanks
> Patrick


-- 
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] Confusing order by error

[Brian Dunavant]

From the docs you linked:

"Each expression can be the name or ordinal number of an output column
(SELECT list item), or it can be an arbitrary expression formed from
input-column values."

The "name" in your order by is a reference to the output column.  The
following example shows the same with "foo" instead of name.  Once you
use UPPER() it is now an arbitrary expression where the 'name' you are
referring to becomes ambiguous.

SELECT t1.pk, t1.name as foo, t1.ref, CONCAT( t2.id , ':', t2.name )
AS ref_display
  FROM test_table as t1
 LEFT JOIN test_table as t2 ON  t1.ref = t2.pk
  ORDER BY foo;
 pk |  foo   | ref | ref_display
++-+-
  2 | barney |   1 | 1000:fred
  3 | betty  |   2 | 2000:barney
  1 | fred   | | :
  4 | wilma  |   1 | 1000:fred
(4 rows)

Someone may correct me if I'm wrong here, but since "name" matches an
output column, it assumes that is what you mean and doesn't bother to
consider that the output column happens to have the same name as a
column in the source tables.


On Fri, Mar 31, 2017 at 3:39 PM,   wrote:
> I'm hoping someone can give us a little help understanding an error in the 
> ORDER BY clause, because when I read 
> https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-ORDERBY I just 
> don't see anything that explains the behavior.
>
> This is with Pg-9.5.1 on Centos (not that I think the OS matters here).
>
> Consider this table and data, stripped down example of real code:
>
> CREATE TABLE IF NOT EXISTS test_table (
>pkINTEGER PRIMARY KEY,
>idINTEGER NOT NULL,
>name  TEXTNOT NULL,
>ref   INTEGER REFERENCES test_table
> );
>
> INSERT INTO test_table
>( pk, id, name, ref )
> VALUES
>( 1, 1000, 'fred',null ),
>( 2, 2000, 'barney',  1 ),
>( 3, 3000, 'betty',   2 ),
>( 4, 4000, 'wilma',   1 )
> ON CONFLICT DO NOTHING;
>
> select * from test_table;
>
>  pk |  id  |  name  | ref
> +--++-
>   1 | 1000 | fred   |
>   2 | 2000 | barney |   1
>   3 | 3000 | betty  |   2
>   4 | 4000 | wilma  |   1
> (4 rows)
>
> So far so good, but when we try to use the data in a more meaningful way:
>
> SELECT t1.pk, t1.name, t1.ref, CONCAT( t2.id , ':', t2.name )  AS 
> ref_display
>   FROM test_table as t1
>  LEFT JOIN test_table as t2 ON  t1.ref = t2.pk
>   ORDER BY name;
>
>  pk |  name  | ref | ref_display
> ++-+-
>   2 | barney |   1 | 1000:fred
>   3 | betty  |   2 | 2000:barney
>   1 | fred   | | :
>   4 | wilma  |   1 | 1000:fred
> (4 rows)
>
> That looks reasonable ... but if we change the ORDER BY clause to normalize 
> should the name be mixed case:
>
> SELECT t1.pk, t1.name, t1.ref, CONCAT( t2.id , ':', t2.name )  AS 
> ref_display
>   FROM test_table as t1
>  LEFT JOIN test_table as t2 ON  t1.ref = t2.pk
>   ORDER BY UPPER(name);
>
> ERROR:  column reference "name" is ambiguous
> LINE 4:   ORDER BY UPPER(name);
>  ^
>
> Eh? The parser (or whatever phase) understood "ORDER BY name" in the first 
> query, so why did that UPPER() string function make a difference in the 
> second query?
>
> I can almost make sense of it in that when the result tuples are created as 
> it works, there are 2 name fields present: t1.name & t2.name. In the first 
> example they should have the same value but in the second they'd potentially 
> have different values (1 raw and 1 up-cased). But that also doesn't really 
> make sense either as I'd think the first query should have the same issue. 
> I'd think (obviously incorrectly :) that we'd get either both working or both 
> failing, not 1 works while the other fails.
>
> So what's going on here?
>
> Thanks,
> Kevin
>
>
> --
> 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] Request to add feature to the Position function

[Brian Dunavant]

Putting together Adrian Klaver's, and David Johnson's suggestions I
think gets to what he was asking for:


#  select length('Tomomasomaa') - position(reverse('om') in
reverse('Tomomasomaa'));
 ?column?
--
   12



On Mon, Mar 27, 2017 at 12:16 PM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 03/27/2017 09:03 AM, Brian Dunavant wrote:
>>
>> That does not return the correct answer for the original poster's request.
>>
>> flpg=# select position('om' in reverse('Tomomasaaa'));
>>  position
>> --
>>15
>> (1 row)
>
>
> It shows the position counting back from the end. If you want counting from
> the front:
>
> aklaver@test=>  select (length('Tomomasaaa')+ 1) - position('om' in
> reverse('Tomomasaaa'));
>  ?column?
> --
> 4
>
>
>>
>>
>>
>>
>> On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com> wrote:
>>>
>>> On 03/27/2017 08:05 AM, Ron Ben wrote:
>>>>
>>>>
>>>> Hi,
>>>> position(substring in string)
>>>> as listed here:
>>>> https://www.postgresql.org/docs/9.1/static/functions-string.html
>>>> locates sub string in a string.
>>>>
>>>> It doesn't support locateing the substring from the back.
>>>>
>>>> For example:
>>>>
>>>> position('om' in 'Tomomas')
>>>> gives 2
>>>>
>>>> But if I want to locate the first occurance from the back of the string
>>>> it's impossible/
>>>
>>>
>>>
>>> aklaver@test=> select position('om' in reverse('Tomomas'));
>>>  position
>>> --
>>> 4
>>>
>>>
>>>>
>>>> My suggestion is to create a function
>>>> position(substring in string,order)
>>>> where order can be: begin, end
>>>>
>>>> and it will find the string according to this parameter.
>>>> This is pretty easy to implement and should be a part of the PostgreSQL
>>>> tools.
>>>>
>>>> similar fuctionality exists in trim function where user can specify
>>>> leading or taling parameter
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.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...@aklaver.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] Request to add feature to the Position function

[Brian Dunavant]

That does not return the correct answer for the original poster's request.

flpg=# select position('om' in reverse('Tomomasaaa'));
 position
--
   15
(1 row)




On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver
 wrote:
> On 03/27/2017 08:05 AM, Ron Ben wrote:
>>
>> Hi,
>> position(substring in string)
>> as listed here:
>> https://www.postgresql.org/docs/9.1/static/functions-string.html
>> locates sub string in a string.
>>
>> It doesn't support locateing the substring from the back.
>>
>> For example:
>>
>> position('om' in 'Tomomas')
>> gives 2
>>
>> But if I want to locate the first occurance from the back of the string
>> it's impossible/
>
>
> aklaver@test=> select position('om' in reverse('Tomomas'));
>  position
> --
> 4
>
>
>>
>> My suggestion is to create a function
>> position(substring in string,order)
>> where order can be: begin, end
>>
>> and it will find the string according to this parameter.
>> This is pretty easy to implement and should be a part of the PostgreSQL
>> tools.
>>
>> similar fuctionality exists in trim function where user can specify
>> leading or taling parameter
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] INSERT and ON CONFLICT

[Brian Dunavant]

I believe the following test should answer your question.

db=# create table test ( a integer not null unique );
CREATE TABLE

db=# insert into test values (1);
INSERT 0 1

db=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_a_key"
DETAIL:  Key (a)=(1) already exists.

db=# insert into test values (1) on conflict do nothing;
INSERT 0 0



On Fri, Mar 10, 2017 at 12:35 PM, Rich Shepard  wrote:
>   I'm filling a table with rows and have the first batch successfully
> inserted. When I add more rows there may be some that already exist in the
> table and I would prefer that they be ignored and the insert process
> continue.
>
>   The syntax page for INSERT suggests that ON CONFLICT DO NOTHING is exactly
> what I want to include in the command. Have I correctly interpreted what the
> DO NOTHING option does when a row to be inserted already is present in the
> table?
>
> Rich
>
>
> --
> 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] bitwise storage and operations

[Brian Dunavant]

If it's in integer columns, bitwise logic works just like you would
expect it to as well.
https://www.postgresql.org/docs/current/static/functions-math.html

db=# select 'foo' where (9 & 1) > 0;
 ?column?
--
 foo
(1 row)

db=# select 'foo' where (9 & 2) > 0;
 ?column?
--
(0 rows)

Just bit-wise AND them and compare if the result is > 0.  If you use
the bitshift operator (<<) make sure you use parens to force ordering.
This is important.

On Mon, Sep 26, 2016 at 7:34 PM, David G. Johnston
 wrote:
> Please include the list in all replies.
>
> On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco  wrote:
>>
>>
>> On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote:
>>
>> On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco 
>> wrote:
>>>
>>> The documentation doesn't have any examples for SELECT for the bitwise
>>> operators,
>>
>>
>> That shows a simple computation.  One can "SELECT" any computation and get
>> a value.
>>
>> It doesn't show a bitwise operator being used against an INT or BIT
>> column, as I further elaborated.
>
>
> I assumed a certain level of familiarity with databases and provided enough
> info to answer your main question: "what are the available bit string
> operators?".  That you can apply these operator to either constants or
> columns was knowledge I took for granted.
>
>>
>> From what I can tell so far, i need to extract and compare a substring for
>> the (reverse) index of the particular bit I want to filter on.
>
>
> B'1001' is typed bit(4)...
>
> The only requirement with a WHERE clause is that the computation must result
> in a boolean.  My example SELECT computation does just that.  It uses
> "varbit" for convenience but INT can be CAST() to BIT and the operators
> themselves should operate on any of the BIT variants.
>
> What you want is the "bit-wise AND" operator and the equality operator, both
> of which you were shown.
>
> I'd suggest you put forth your own example, filling in pseudo-code where
> needed, if you wish for more specific advice.
>
> David J.
>


-- 
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] CoC [Final v2]

[Brian Dunavant]

>> Participation does not need to be limited to copy-editing.  Of all the
>> ways to develop a community CoC, we're engaged in just about the worst
>> possible one right now.
>
> so what would be a better way of developing this ?

Of interesting note, the Ruby community is currently considering
switching to a CoC inspired directly from this draft of a Postgres
CoC.   The extremely long conversation can be viewed at:

https://redmine.ruby-lang.org/issues/12004


-- 
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] CoC [Final]

[Brian Dunavant]

> * Participants who disrupt the collaborative space, or participate in
> a pattern of behaviour which could be considered harassment will not
> be tolerated.

Perhaps changing the ", or participate" to " by engaging" would make
that statement more focused.

> "Disrupting the collaborative space" is very hard to define even when
> nobody has an agenda. When there are agendas, it almost certainly will
> lead to selective enforcement.

PHP is currently going through a CoC discussion as well.  Paul Jones
has a good blog post on the dangers of CoC's and their abuse.

http://paul-m-jones.com/archives/6214


-- 
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] WIP: CoC

[Brian Dunavant]

>> "3)  A safe, respectful, productive and collaborative environment is
>> free of negative personal criticism directed at a member of a
>> community, rather than at the technical merit of a topic."
>>

> A safe, respectful, productive and collaborative environment is free
> of non-technical or personal comments related to gender, sexual orientation,
> disability, physical appearance, body size or race.

Between these two I still prefer my wording here because it
encompasses all personal attacks regardless of topic or type and
avoids hot-button words that distract from the point and can be used
for lawyering.  It also emphasizes the desired behavior instead, that
criticism should be about the technical merit of the topic.  "Don't be
a jerk, and stick to the code."  Maybe even rewording it to be a
positive instead of a negative would improve it further.

"A safe, respectful, productive and collaborative environment is one
that focuses on the technical merit of ideas and solutions rather than
on the person behind them."


-- 
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] WIP: CoC

[Brian Dunavant]

> 3. A safe, respectful, productive and collaborative environment is free
> comments related to gender, sexual orientation, disability, physical
> appearance, body size or race.

I think you meant "free OF comments".

However it still picks a few special classes of complaint, some of
which cause ambiguity such as 'gender'.  Does that mean I can't use
"he/she" pronouns?  It also implies that i'm allowed to criticize
people in other ways, say, their political affiliation or country.
Rather than list a bunch of "no no" perhaps something like:

"3)  A safe, respectful, productive and collaborative environment is
free of negative personal criticism directed at a member of a
community, rather than at the technical merit of a topic."


-- 
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] Code of Conduct: Is it time?

[Brian Dunavant]

> We expect of everyone in our spaces to try their best to do the same in a
> kind and gentle manner. If you feel it's just a minor offense and the person
> didn't mean harm by it,
>
> simply ignore it unless the pattern of talk continues. If the person
> continues or they say something you feel is very offensive or degrading to
> another,
>
> tell a project maintainer preferably off-list and we will talk with the
> person to affect a change in their behavior or kick them out if we determine
> behavior change is not possible.

I am concerned about this particular wording as it implicitly assumes
that the offended party is correct based on how they 'feel' and
requires punishment of/change by the 'offender' regardless of the
severity, or even validity of the claim.  I don't think that is the
intent, but that is how it reads (to me).


-- 
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] Bi-Directional replication(BDR)

[Brian Dunavant]

I would suggest going to  http://bdr-project.org/docs/stable/index.html

On Tue, Aug 4, 2015 at 3:47 PM,  clingare...@vsoftcorp.com wrote:
 Hi,



 Please help me on:



 what is the use of bidirectional replication in PostgreSQL?

 How BDR works?

 how to setup BDR?

 on which versions BDR works?



 Thanks  regards,

 Chandra kiran



 Please do not print this email unless it is absolutely necessary. This email
 and any files transmitted with it are confidential and intended solely for
 the use of the individual or entity to which they are addressed. If you are
 not the intended recipient, you should not disseminate, distribute or copy
 this e-mail. Please notify the sender immediately by e-mail and destroy all
 copies of this message and any attachments. Any views or opinions presented
 in this email are solely those of the author and do not necessarily
 represent those of the company. Warning: Although the company has taken
 reasonable precautions to ensure no viruses are present in this email, the
 company cannot accept responsibility for any loss or damage arising from the
 use of this email or attachments.


-- 
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] date with month and year

[Brian Dunavant]

On Thu, May 21, 2015 at 5:27 PM, Thomas Kellerer spam_ea...@gmx.net wrote:
 Postgres does not store the time zone. When storing a timestamp with time
 zone, it
 is normalized to UTC based on the timezone of the client. When you retrieve
 it,
 it is adjusted to the time zone of the client.


Sorry, I misspoke.  Thank you for correcting it.  It is storing it as
UTC time zone.  The rest of my post still applies.  You will get the
wrong wall-clock time for the future date because it is stored as UTC
and the conversion rules will have changed giving you a different time
when you convert it back to the local time zone.


-- 
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] date with month and year

[Brian Dunavant]

It's probably worth noting that both the Ruby 'best practice' AND
Postgres have a failure case when dealing with future dates precisely
because they are storing the data as UTC with a time zone.  This is
one case where storing the data WITHOUT TIME ZONE would actually save
your bacon.

From the postgres docs:  For times in the future, the assumption is
that the latest known rules for a given time zone will continue to be
observed indefinitely far into the future.

Imagine scheduling a meeting for a certain time a few years from now.
 This will be stored as UTC + time zone.   A year later, that
government decides to change the time zone rules for their country.
Your operating system will get the new timezone data in an update (as
it should).  However when the meeting comes around, you're going to be
early/late because the wall time that you get converting back from
UTC+time zone is no longer the time that you were supposed to have
been at the meeting.   If you had stored that future date as a
timestamp WITHOUT time zone you would have still been on-time.

This is only an issue for future dates, not past ones.

-Brian Dunavant
(time is hard, so if I'm wrong anywhere here, someone please correct me)


On Thu, May 21, 2015 at 2:56 PM, Steve Crawford
scrawf...@pinpointresearch.com wrote:
 On 05/21/2015 10:45 AM, Paul Jungwirth wrote:

 You really shouldn't use WITHOUT TIME ZONE.


 I'd like to know more about this. Can you say why?


 Start by reading about the date and time data types with special attention
 to section 8.5.3:
 www.postgresql.org/docs/current/static/datatype-datetime.html

 Now go back and read it again and experiment a while until it makes sense.
 As Adrian Klaver so eloquently put it, If I have learned anything about
 dealing with dates and times, is that it is a set of exceptions bound
 together by a few rules. Every time you think you have the little rascals
 cornered, one gets away. This is also a very good reason to avoid
 reinventing the wheel.

 When you need a break, watch this:
 https://www.youtube.com/watch?v=-5wpm-gesOY

 His conclusion is a good one: be very happy that someone else has done the
 dirty work for you.

 The Ruby article does make one good point which is that we are talking about
 what they call an instant or what I like to refer to as a point in time.
 The point in time is actually a better way of thinking of timestamp with
 time zone since the timestamp with time zone does not actually store any
 timezone information - it stores a point in time that can be manipulated in
 the time-zone of your choosing whereas timestamp without time zone is not a
 point in time and must be combined with other information to do proper
 manipulation.

 The article does also display a couple attitudes that I feel are especially
 rampant in the web-development community. The first is that web developers
 shouldn't become educated about the capabilities of a database but rather
 use the database as a dumb data-store and redo everything themselves (often
 this includes an utter failure to use the data-integrity capabilities of the
 database).

 The second is the assumption that they are the only users of the database
 and that nobody will ever access the data except through their
 custom-written Ruby/PHP/Perl/Python code and that no other programming
 language will ever be used. Woe be to the poor slob who has to deal with
 ad-hoc queries, analytics platforms or reporting systems that weren't so
 brilliantly reinvented or who wants to use range-types or other nice
 PostgreSQL features.

 Internally PostgreSQL stores timestamp without time zone in UTC but that is
 entirely irrelevant. What is relevant is that you can provide an
 instant/point in time in whatever time-zone representation you want and
 get it back the same way. Want to use a Unix epoch in your code. Go ahead:
 extract(epoch from yourtstzcol)
 abstime(yourepochint)

 Want to assume everything is UTC? No problem:
 set timezone to 'UTC';

 Then you can reinvent wheels to your heart's content without wrecking the
 ability to easily use other tools.

 By the way, use full timezone names to avoid ambiguity. I don't know what
 Ruby cooked up but PostgreSQL uses industry-standard names:
 select * from pg_timezone_names;

 Your original question had to do with month/year. You will have to define
 this for your use-case but beware that it won't necessarily get you away
 from time-zone issues as the month ticks over on a zone-by-zone basis.

 Also note that time-intervals can be a source of interesting side-effects.
 Operator precedence is important. For example, what is one month? 28-days?
 29? 30? 31? Every system must make a judgment call. Add a month to January
 31 and you will get February 28. But add/subtract a month from February 28
 and you get January 28/March 28. So you can create a query that takes a
 date, adds a month and subtracts a month and results in a different date.
 There is nothing to do here but to read the docs

Re: [GENERAL] Some indexing advice for a Postgres newbie, please?

[Brian Dunavant]

You should consider a BitString.
http://www.postgresql.org/docs/9.4/static/datatype-bit.html

On Thu, Feb 19, 2015 at 11:10 AM, brian br...@meadows.pair.com wrote:

 Hi folks,

 I have a single-user application which is growing beyond the
 fixed-format data files in which it currently holds its data, I need a
 proper database as the backend. The front end is written using Lazarus
 and FreePascal under Linux, should anyone feel that makes a
 difference. The database will need to grow to around 250,000 records.

 My problem is with the data field which is the (unique) key. It's
 really a single 192-bit integer (it holds various bits of bitmapped
 data) which I currently hold as six 32-bit integers, but can convert
 if needed when transferring the data.

 How would you advise that I hold this field in a Postgres database,
 given the requirement for the whole thing to be a unique key? The
 first 64 bits change relatively infrequently, the last 128 bits will
 change with virtually every record. The last 128 bits will ALMOST be
 unique in themselves, but not quite. :(

 Thanks,

 Brian.


 --
 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] SELECT, GROUP BY, and aggregates

[Brian Dunavant]

To lower the amount of time spent copy pasting aggregate column names,
it's probably worth noting Postgres will allow you to short cut that
with the column position.  For example:

select long_column_name_A, long_column_name_b, count(1)
from foo
group by 1,2
order by 1,2

This works just fine.  It's not in the spec, but postgres supports it.
I'll leave it to others to argue about it being a best practice or
not.


On Fri, Feb 13, 2015 at 1:57 PM, Bill Moran wmo...@potentialtech.com wrote:
 On Fri, 13 Feb 2015 10:48:13 -0800
 Jeff Janes jeff.ja...@gmail.com wrote:

 On Fri, Feb 13, 2015 at 10:26 AM, Bill Moran wmo...@potentialtech.com
 wrote:

   Ryan Delaney ryan.dela...@gmail.com writes:
Why couldn't an RDBMS such as postgres interpret a SELECT that omits
  the GROUP
BY as implicitly grouping by all the columns that aren't part of an
  aggregate?
 
  I'm Mr. Curious today ...
 
  Why would you think that such a thing is necessary or desirable? Simply
  add the
  columns to the GROUP BY clause and make the request unambiguous.

 Where would the ambiguity be?

 With a large, complex query, trying to visually read through a list of
 column selections to figure out which ones _aren't_ aggregated and will
 be auto-GROUP-BYed would be ... tedious and error prone at best.

 You're right, though, it wouldn't be ambiguous ... that was a poor
 choice of words on my part.

 I waste an inordinate amount of time retyping select lists over into the
 group by list, or copying and pasting and then deleting the aggregate
 clauses.

 Interesting ... I've never kept accurate track of the time I spend doing
 things like that, but inordinate seems like quite a lot.

 In my case, I'm a developer so I would tend toward creating code on the
 client side that automatically compiled the GROUP BY clause if I found
 that scenarios like you describe were happening frequently. Of course,
 that doesn't help a data anaylyst who's just writing queries

 It is an entirely pointless exercise.  I can't fault PostgreSQL
 for following the standard, but its too bad the standards aren't more
 sensible.

 I can't speak to the standard and it's reasons for doing this, but there
 are certainly some whacko things in the standard.

 Thanks for the response.

 --
 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


-- 
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] Fwd: Ask for a question

[Brian Dunavant]

This is not quite true.  I don't believe there are any flight
simulator easter-eggs hidden inside the Postgres code.  :)

On Wed, Jan 21, 2015 at 10:59 AM, Rémi Cura remi.c...@gmail.com wrote:
 More bluntly maybe :

 if you can do it in Excel,
 you can do it in Postgres.

 Cheers,
 Rémi-C

 2015-01-21 16:37 GMT+01:00 Raymond O'Donnell r...@iol.ie:

 On 21/01/2015 14:38, Pierre Hsieh wrote:
 
 
  Hi,
 
 
 
  Would you please tell me whether PostgreSQL can execute the following
  tasks? If not, please also tell me which one can help me for that.
  Thanks


 Not clear what you're asking, but if you just want to find the standard
 deviation of a sample then that's no problem:


 http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE

 Hope this helps,

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie


 --
 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] Simple Atomic Relationship Insert

[Brian Dunavant]

What issue are you having?  I'd imagine you have a race condition on
the insert into hometowns, but you'd have that same race condition in
your app code using a more traditional 3 query version as well.

I often use CTEs like this to make things atomic.  It allows me to
remove transactional code out of the app and also to increase
performance by reducing the back-and-forth to the db.
http://omniti.com/seeds/writable-ctes-improve-performance



On Tue, Jan 13, 2015 at 4:21 PM, Robert DiFalco
robert.difa...@gmail.com wrote:
 This CTE approach doesn't appear to play well with multiple concurrent
 transactions/connections.

 On Tue, Jan 13, 2015 at 10:05 AM, John McKown john.archie.mck...@gmail.com
 wrote:

 On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco
 robert.difa...@gmail.com wrote:

 Thanks John. I've been seeing a lot of examples like this lately. Does
 the following approach have any advantages over traditional approaches?

 WITH sel AS (
 SELECT id FROM hometowns WHERE name = 'Portland'
 ), ins AS (
   INSERT INTO hometowns(name)
 SELECT 'Portland'
 WHERE NOT EXISTS (SELECT 1 FROM sel)
   RETURNING id
 )
 INSERT INTO users(name, hometown_id)
 VALUES ('Robert', SELECT id FROM ins UNION ALL SELECT id FROM sel);



 Oh, that is very clever. I've not see such a thing before. Thanks.

 I've added it to my stable of tricks. Which aren't really tricks, just
 really nice new methods to do something.

 The main advantage that I can see is that it is a single SQL statement to
 send to the server. That makes it self contained so that it would be more
 difficult for someone to accidentally mess it up. On the other hand, CTEs
 are still a bit new (at least to me) and so the why it works might not be
 very obvious to other programmers who might need to maintain the
 application. To many this lack of obviousness is a detriment. To me, it
 means update your knowledge. But then, I am sometimes a arrogant BOFH. Add
 that to my being an surly old curmudgeon, and you can end up with some bad
 advice when in a corporate environment. The minus, at present, is that it
 is clever and so may violate corporate coding standards due to
 complexity. Or maybe I just work for a staid company.

 --
 While a transcendent vocabulary is laudable, one must be eternally careful
 so that the calculated objective of communication does not become ensconced
 in obscurity.  In other words, eschew obfuscation.

 111,111,111 x 111,111,111 = 12,345,678,987,654,321

 Maranatha! 
 John McKown




-- 
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] Simple Atomic Relationship Insert

[Brian Dunavant]

With the single CTE I don't believe you can do a full upsert loop.  If
you're doing this inside of a postgres function, your changes are
already atomic, so I don't believe by switching you are buying
yourself much (if anything) by using a CTE query instead of something
more traditional here.

The advantages of switching to a CTE would be if this code was all
being done inside of the app code with multiple queries.

On Tue, Jan 13, 2015 at 4:39 PM, Robert DiFalco
robert.difa...@gmail.com wrote:
 Well, traditionally I would create a LOOP where I tried the SELECT, if there
 was nothing I did the INSERT, if that raised an exception I would repeat the
 LOOP.

 What's the best way to do it with the CTE? Currently I have the following
 which gives me Duplicate Key Exceptions when two sessions try to insert the
 same record at the same time.

 CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS
 INTEGER AS $
 DECLARE hometown_id INTEGER;
 BEGIN
   WITH sel AS (
   SELECT id FROM hometowns WHERE name = hometown_name
   ), ins AS (
 INSERT INTO hometowns (name)
   SELECT hometown_name
   WHERE NOT EXISTS(SELECT 1 FROM sel)
 RETURNING id
   )
   SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel;
   RETURN hometown_id;
 END;
 $ LANGUAGE plpgsql;

 And that is no bueno. Should I just put the whole thing in a LOOP?


-- 
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] Simple Atomic Relationship Insert

[Brian Dunavant]

The loop to run it twice handles that yes.  I don't think that buys
you anything over a more traditional non-cte method though.  I'd run
them a few thousand times to see if there's any difference in runtimes
but my guess is the CTE version would be slightly slower here.

v_id integer;
BEGIN;
  select id into v_id from hometowns where name = hometown_name;
  BEGIN
insert into hometowns (name)
select hometown_name where v_id is null
returning id into v_id;
  EXCEPTION WHEN unique_violation
  THEN
 select id into v_id from hometowns where name = hometown_name;
  END;
  insert into users (name, hometown_id)
  values ('Robert', v_id);
END;

On Tue, Jan 13, 2015 at 5:06 PM, Robert DiFalco
robert.difa...@gmail.com wrote:
 This seems to get rid of the INSERT race condition.

 CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS
 INTEGER AS $
 DECLARE hometown_id INTEGER;
 BEGIN
   LOOP
 BEGIN
   WITH sel AS (
   SELECT id FROM hometowns WHERE name = hometown_name
   ), ins AS (
 INSERT INTO hometowns (name)
   SELECT hometown_name
   WHERE NOT EXISTS(SELECT 1 FROM sel)
 RETURNING id
   )
   SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel;
   RETURN hometown_id;

 EXCEPTION WHEN unique_violation
   THEN
 END;
   END LOOP;
 END;
 $ LANGUAGE plpgsql;


 On Tue, Jan 13, 2015 at 1:53 PM, Brian Dunavant br...@omniti.com wrote:

 With the single CTE I don't believe you can do a full upsert loop.  If
 you're doing this inside of a postgres function, your changes are
 already atomic, so I don't believe by switching you are buying
 yourself much (if anything) by using a CTE query instead of something
 more traditional here.

 The advantages of switching to a CTE would be if this code was all
 being done inside of the app code with multiple queries.

 On Tue, Jan 13, 2015 at 4:39 PM, Robert DiFalco
 robert.difa...@gmail.com wrote:
  Well, traditionally I would create a LOOP where I tried the SELECT, if
  there
  was nothing I did the INSERT, if that raised an exception I would repeat
  the
  LOOP.
 
  What's the best way to do it with the CTE? Currently I have the
  following
  which gives me Duplicate Key Exceptions when two sessions try to insert
  the
  same record at the same time.
 
  CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR)
  RETURNS
  INTEGER AS $
  DECLARE hometown_id INTEGER;
  BEGIN
WITH sel AS (
SELECT id FROM hometowns WHERE name = hometown_name
), ins AS (
  INSERT INTO hometowns (name)
SELECT hometown_name
WHERE NOT EXISTS(SELECT 1 FROM sel)
  RETURNING id
)
SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel;
RETURN hometown_id;
  END;
  $ LANGUAGE plpgsql;
 
  And that is no bueno. Should I just put the whole thing in a LOOP?




-- 
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] Simple Atomic Relationship Insert

[Brian Dunavant]

A very good point, but it does not apply as here (and in my article)
we are not using updates, only insert and select.



On Tue, Jan 13, 2015 at 6:03 PM, Thomas Kellerer spam_ea...@gmx.net wrote:
 Brian Dunavant wrote on 13.01.2015 22:33:

 What issue are you having?  I'd imagine you have a race condition on
 the insert into hometowns, but you'd have that same race condition in
 your app code using a more traditional 3 query version as well.

 I often use CTEs like this to make things atomic.  It allows me to
 remove transactional code out of the app and also to increase
 performance by reducing the back-and-forth to the db.
 http://omniti.com/seeds/writable-ctes-improve-performance


 Craig Ringer explained some of the pitfalls of this approach here:

 http://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates

 which is a follow up question based on this:
 http://stackoverflow.com/a/8702291/330315

 Thomas





 --
 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

[GENERAL] Question about partial functional indexes and the query planner

[Brian Dunavant]

Hi everyone,

I am using a partial functional index on a table where F(a) = a.  Querying
whre F(a) = a hits the index as expected.  However the reverse statement a
= F(a) does not.  I have verified this in 9.3.4.

Is this a deficiency with the query planner, or are these not actually
equivalent?  I've been stumped on it.

-Brian Dunavant

Test script to display behavior below:


-- Setup the test data
CREATE OR REPLACE FUNCTION public.return_if_even(v_id integer) returns
integer
LANGUAGE sql AS
$$
SELECT case when v_id % 2 = 1 then 0 else v_id end;
$$;

create table public.partial_functional_index_test as
select id from generate_series(1,100) AS s(id);

create index partial_functional_idx ON public.partial_functional_index_test
USING btree ( public.return_if_even(id) )
WHERE public.return_if_even(id) = id;

-- This will hit the index
explain analyze select count(1) from public.partial_functional_index_test
where public.return_if_even(id) = id;

-- This will not hit the index
explain analyze select count(1) from public.partial_functional_index_test
where id = public.return_if_even(id);


-- To work around it, I can index both ways:
drop index partial_functional_idx;

create index partial_functional_idx ON public.partial_functional_index_test
USING btree ( public.return_if_even(id) )
WHERE public.return_if_even(id) = id OR id = public.return_if_even(id);

-- Now both versions will hit the index
explain analyze select count(1) from public.partial_functional_index_test
where public.return_if_even(id) = id;
explain analyze select count(1) from public.partial_functional_index_test
where id = public.return_if_even(id);

-- Cleanup test data
drop table public.partial_functional_index_test;
drop function public.return_if_even(integer);