Re: [GENERAL] to pg

[Tom Lane]

Alban Hertroys  writes:
> On 25 September 2015 at 13:08, Ramesh T  wrote:
>> CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y' then
>> load_id else null end );
>> 
>> how can i convert case expressed to postgres..above it is oracle.

> BTW, your CASE statement isn't exactly valid, even in Oracle. Your
> comparison is in fact this: picked = picked='y'.

Yeah.  Aside from that confusion, the other reason this command doesn't
work as-is is you need more parentheses.  An expression in an index has
to either look like a function call or be parenthesized.  So:

regression=# create table pick (picked text, load_id int);
CREATE TABLE
regression=# CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when 
picked='y' then load_id else null end );
ERROR:  syntax error at or near "case"
regression=# CREATE UNIQUE INDEX idx_load_pick ON  pick ((case picked when 
picked='y' then load_id else null end ));
ERROR:  operator does not exist: text = boolean
regression=# CREATE UNIQUE INDEX idx_load_pick ON  pick ((case when picked='y' 
then load_id else null end ));
CREATE INDEX

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] Extract giving wrong week of year

[Tom Lane]

Ben Primrose  writes:
> I’m seeing some odd behavior from the extract function.
> Any idea why extract would think January 3rd of next year is the 53rd week
> of the year?

See the fine manual ...
http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

  week

The number of the ISO 8601 week-numbering week of the year. By
definition, ISO weeks start on Mondays and the first week of a year
contains January 4 of that year. In other words, the first Thursday of
a year is in week 1 of that year.

In the ISO week-numbering system, it is possible for early-January
dates to be part of the 52nd or 53rd week of the previous year, and
for late-December dates to be part of the first week of the next
year. For example, 2005-01-01 is part of the 53rd week of year 2004,
and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31
is part of the first week of 2013. It's recommended to use the isoyear
field together with week to get consistent results.


Depending on what you want to define a "week" as, you can probably get
what you want from some other EXTRACT() property. "doy" div 7 might
help, for example.

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

[GENERAL] Extract giving wrong week of year

[Ben Primrose]

All,

I’m seeing some odd behavior from the extract function.
Any idea why extract would think January 3rd of next year is the 53rd week
of the year?

Thanks

Ben



[bprimrose@primrose partitioning]$ psql

Null display is "¤".

Line style is unicode.

Output format is wrapped.

Timing is on.

psql (9.3.9)

Type "help" for help.



bprimrose@home=> select extract (week from '2016-01-03
00:01:00'::timestamp);

date_part

───

53

(1 row)



Time: 1.128 ms

bprimrose@home=> select version();


version

─

PostgreSQL 9.3.9 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2
20150212 (Red Hat 4.9.2-6), 64-bit

(1 row)

[GENERAL] Listen/notify, equivalents on other DBs

[Mark Morgan Lloyd]

I'm trying to get support for PostgreSQL's listen/notify into a 
development environment, but since it supports multiple database 
backends: can anybody comment on how many other servers have a 
comparable facility?


Minimal research has allowed me to code something for 
Firebird/Interbase, but I'm not in a position to investigate Oracle, 
IBM, MS and the rest. I'd appreciate any general comments from somebody 
who has broad SQL experience, I'm not asking for example code.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


--
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] Postgresql HA questions

[Israel Brewster]

Thanks. I'd taken some stabs at setting up Corosync/Pacemaker, but could never get the PostgreSQL portion to work properly. One difficulty is that I am using named replication slots, which don't appear to be supported. The other issue is that the system tended to try to start my secondary as primary, which of course doesn't work to bring up the old primary as secondary (at least, that's my understanding. Let me know if that's wrong).I'll take another stab at it given the steps outlined in the presentation you posted, and given your success - I must just be doing something wrong here. 
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD


On Sep 25, 2015, at 1:46 AM, Steve Pritchard  wrote:Israel,I can't answer all your questions, but we've just set up a HA pair with Hot Standby using Corosync/Pacemaker. However we haven't deployed this 'live' yet. We originally found a presentation from The PostgreSQL Conference PostgreSQL High Availability with Corosync/Pacemaker, and then bought the book PostgreSQL 9.0 High Performance (a sample chapter is available as a PDF).All working out well in testing at the moment.Steve PritchardBritish Trust for OrnithologyOn 23 September 2015 at 17:36, Israel Brewster  wrote:With my application servers, I have a system set up using corosync and pacemaker that allows for seamless fail-over between the two machines, with the IP address and all services moving smoothly between the two at will. Ideally, I would have a similar setup with my database servers, so the applications never even know that there was a switch. Is this possible with Postgresql at all? Does it make a difference that at least one app has an "always on" connection to the DB Server?

Re: [GENERAL] Automatically Updatable Foreign Key Views

[Raymond Brinzer]

Well, those are two high-quality answers, and I appreciate them.  Not
really the news I was hoping for, of course, though I suppose it's a
small consolation that the problem is not in the model, but the
implementation.  That leaves the possibility open in principle, at
least, though the technical details aren't promising.

I need to think through what's been said; possibly I'll have a
follow-up question or two later.  At any rate, thank you both.

On Thu, Sep 24, 2015 at 10:23 AM, Tom Lane  wrote:
> David Rowley  writes:
>> On 24 September 2015 at 13:32, Raymond Brinzer 
>> wrote:
>>> Any thoughts on this would be welcome.
>
>> The problem is that an UPDATE/DELETE could take place which causes the
>> foreign key to be violated and you may try and perform an UPDATE to the
>> view before the foreign key is cascaded by the trigger at end of
>> statement/transaction. Remember that a statement could execute inside of a
>> volatile function being called by some outer query.
>
> Yeah.  We discussed this awhile back in the context of a proposal to
> optimize query plans on the assumption that foreign-key constraints hold
> (which would allow joins to be removed in some cases).  That proposal was
> to only apply the optimization if there were no unfired trigger events in
> the current transaction, which would imply that there were no unperformed
> foreign key checks.  That's valid as far as it goes, and you could imagine
> narrowing the restriction even more by checking to see if there were
> specifically any FK triggers queued for the query's table(s).  However the
> potential delay between planning and execution made it a real mess to be
> sure if the optimization is safe, so I kind of doubt that it'll ever be
> accepted.
>
> In this context, using a similar approach would mean that it would be
> state-dependent whether an update on a view was allowed at all, which
> seems way too messy IMO.  Even worse, if one update was allowed then
> the next one would not be, because the update on the view's underlying
> table would have queued FK check trigger events.
>
> In fact, I think this means an auto update through the view couldn't be
> allowed to update more than one row, because the first row update might
> have invalidated the FK constraint thus breaking the assumption needed
> for the second update to be well-defined.  That statement is independent
> of any particular implementation approach.  There are probably ways around
> that, such as not allowing the FK-involved columns to be auto updatable,
> but it's really looking like a mess.
>
> regards, tom lane



-- 
Ray Brinzer


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

[GENERAL] Test failover for PosgreSql 9.2

[yuryu]

Hello,
I am new to posgresql.
I've configured replication for my database . It li working fine, but I need
to test failover capabilities.
According to manual I have to kill completely Master and "touch" a trigger
to make Slave new Master.

Could you please recommend me a way to kill Master in order to test
failover?

Thanks, yuryu.




--
View this message in context: 
http://postgresql.nabble.com/Test-failover-for-PosgreSql-9-2-tp5867383.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] to pg

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ramesh T
Sent: Friday, September 25, 2015 7:09 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] to pg

CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y' then 
load_id else null end );

how can i convert case expressed to postgres..above it is oracle.

any help appreciated...



CREATE UNIQUE INDEX idx_load_pick ON  pick (load_id) where picked='y';

Regards,
Igor Neyman

Re: [GENERAL] Test failover for PosgreSql 9.2

[Francisco Reyes]

On 09/25/2015 11:20 AM, yuryu wrote:

According to manual I have to kill completely Master and "touch" a trigger
to make Slave new Master.


You don't have to do anything in the master. If you have configured the 
slave to check for a file, then it will become Read Write when that file 
is created.


You can also do
pg_ctlcluster #.# main promote

Where #.# is version like
pg_ctlcluster 9.3 main promote

In the slave you can run this to check if it is in read only 
(replicating) or read write

select pg_is_in_recovery();


--
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] to pg

[Geoff Winkless]

Surely just

CASE picked WHEN 'y' THEN load_id ELSE NULL END

or

CASE WHEN picked='y' THEN load_id ELSE NULL END

?

On 25 September 2015 at 12:08, Ramesh T 
wrote:

> CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y'
> then load_id else null end );
>
> how can i convert case expressed to postgres..above it is oracle.
>
> any help appreciated...
>

[GENERAL] to pg

[Ramesh T]

CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y'
then load_id else null end );

how can i convert case expressed to postgres..above it is oracle.

any help appreciated...

Re: [GENERAL] to pg

[Ladislav Lenart]

On 25.9.2015 13:08, Ramesh T wrote:
> CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y' then
> load_id else null end );
> 
> how can i convert case expressed to postgres..above it is oracle.
> 
> any help appreciated... 

Hello.

And what about a partial unique index as documented here:

http://www.postgresql.org/docs/9.4/static/indexes-partial.html

I.e.:

CREATE UNIQUE INDEX ON  pick (load_id) WHERE picked = 'y';

HTH,

Ladislav Lenart



-- 
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] to pg

[Albe Laurenz]

Ramesh T wrote:
> CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y' then 
> load_id else null end );
> 
> how can i convert case expressed to postgres..above it is oracle.

CREATE TABLE pick (picked char(1), load_id integer);

CREATE FUNCTION picked_loadid(character, integer) RETURNS integer
   IMMUTABLE STRICT LANGUAGE sql AS
   $$SELECT CASE WHEN $1 = 'y' THEN $2 ELSE NULL END$$;

CREATE INDEX idx_load_pick ON pick (picked_loadid(picked, load_id));

*but*

It will only work with queries like:

SELECT * FROM pick WHERE picked_loadid(picked, load_id) IS NOT NULL;

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] Postgresql HA questions

[Steve Pritchard]

Israel,

I can't answer all your questions, but we've just set up a HA pair with *Hot
Standby using Corosync/Pacemaker*. However we haven't deployed this 'live'
yet.

We originally found a presentation from The PostgreSQL Conference PostgreSQL
High Availability with Corosync/Pacemaker
, and then bought
the book PostgreSQL 9.0 High Performance 
 (a sample chapter

is available as a PDF).

All working out well in testing at the moment.

Steve Pritchard
British Trust for Ornithology

On 23 September 2015 at 17:36, Israel Brewster 
wrote:

> 
> With my application servers, I have a system set up using corosync and
> pacemaker that allows for seamless fail-over between the two machines, with
> the IP address and all services moving smoothly between the two at will.
> Ideally, I would have a similar setup with my database servers, so the
> applications never even know that there was a switch. Is this possible with
> Postgresql at all? Does it make a difference that at least one app has an
> "always on" connection to the DB Server?
> 
>

Re: [GENERAL] Dropped connections with pg_basebackup

[Francisco Reyes]

On 09/24/2015 04:29 PM, Sherrylyn Branchaw wrote:

I'm assuming based on the "SSL error" that you have ssl set to 'on'.
What's your ssl_renegotiation_limit? The default is 512MB, but setting
it to 0 has solved problems for a number of people on this list,
including myself.


I have also seen instances were ssl_renegotiation_limit=0 helped and I 
already tried that. Did not help in this case.


Perhaps will try some tests with a non SSL connection. These are 
machines in an internal network so it may not be too much a security 
issue to turn off SSL at least during initial sync.



--
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] to pg

[Alban Hertroys]

On 25 September 2015 at 13:08, Ramesh T  wrote:
> CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y' then
> load_id else null end );
>
> how can i convert case expressed to postgres..above it is oracle.

Assuming that your queries are written in such a way that Oracle is
indeed using that index and you want your queries to use the index as
well in PG:

CREATE UNIQUE INDEX idx_load_pick ON (load_id) WHERE CASE picked WHEN
'y' THEN load_id ELSE NULL END IS NOT NULL;

That's definitely written a bit redundantly, that's Oracle's fault.

If your queries aren't like that, it's as Ladislav wrote. Much simpler in PG!


To make Oracle use your original index, your queries are probably of a
form containing snippets like:

SELECT *
FROM foo
WHERE CASE picked WHEN 'y' THEN load_id ELSE NULL END IS NOT NULL

BTW, your CASE statement isn't exactly valid, even in Oracle. Your
comparison is in fact this: picked = picked='y'.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
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] Dropped connections with pg_basebackup

[Francisco Reyes]

On 09/24/2015 04:34 PM, Alvaro Herrera wrote:

Sherrylyn Branchaw wrote:
Moreover, the default has been set to 0, because the bugs both in our
usage and in OpenSSL code itself seem never to end.  Just disable it.



Set it to 0 and did not help.
Likely will move all machines to have it =0 since I have seen some SSL 
errors in logs.



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