Re: [GENERAL] timestamp check

[Ramesh T]

postgres query
select current_timestamp-
TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||'
'||(SELECT utc_offset  FROM pg_catalog.pg_timezone_names
WHERE name=DATETIMEZOZE1)   , ''-MM-DD HH24'||' '||'MI'||'
'||'SS')::timestamptz

getting result..

[image: Inline image 1]


But in oracle using systimestamp,to_timestamptz and SS TZH is not
supporting  to_timestamp in postgres.

result..

[image: Inline image 2]

diffrence is days displaying in postgres query..i thnk something wrong. is
it..?

any help apprictiated.






On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver 
wrote:

> On 07/10/2015 05:54 AM, Ramesh T wrote:
>
>>
>> select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD
>> HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset  FROM
>> pg_catalog.pg_timezone_names
>> WHERE name='US/Eastern'),'-MM-DD
>> HH24'||':'||'MI'||':'||'SS')::timestamptz;
>>
>> it's not displaying timezone..any help..?
>>
>>
> Forget my previous post, Rays post made me realize the error of my ways,
> namely thinking current_timestamp-to_timestamp was a function.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] timestamp check

[Ramesh T]

okay,i'm executing a query from pgadmin3.

i want display time with timezone.But above query  displaying date and time
not timezone...

On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T 
> wrote:
>
>>
>> select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD
>> HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset  FROM
>> pg_catalog.pg_timezone_names
>> WHERE name='US/Eastern'),'-MM-DD
>> HH24'||':'||'MI'||':'||'SS')::timestamptz;
>>
>> it's not displaying timezone..any help..?
>>
>>
> ​You haven't told us how you are executing the above query.
>
> It also seems like an awfully convoluted answer to whatever query you are
> asking.
>
> David J.
> ​
>
>

Re: [GENERAL] xmltable in postgres like in oracle..?

[Ramesh T]

xml table is the oracle function ..thank you sir

On Sat, Jul 11, 2015 at 11:04 PM, Adrian Klaver 
wrote:

> On 07/11/2015 08:32 AM, Ramesh T wrote:
>
>> Hi,
>>is xmltable available in postgres..?,if not please give me a
>> advice to replace the xmtable in postgres..?
>>
>
> What is xmltable?
>
> Go to:
>
> http://www.postgresql.org/docs/9.4/interactive/index.html
>
> and in the Search field type xml
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] timestamp check

[Ramesh T]

any help..?

On Sun, Jul 12, 2015 at 11:51 AM, Ramesh T 
wrote:

> postgres query
> select current_timestamp-
> TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||'
> '||(SELECT utc_offset  FROM pg_catalog.pg_timezone_names
> WHERE name=DATETIMEZOZE1)   , ''-MM-DD HH24'||' '||'MI'||'
> '||'SS')::timestamptz
>
> getting result..
>
> [image: Inline image 1]
>
>
> But in oracle using systimestamp,to_timestamptz and SS TZH is not
> supporting  to_timestamp in postgres.
>
> result..
>
> [image: Inline image 2]
>
> diffrence is days displaying in postgres query..i thnk something wrong. is
> it..?
>
> any help apprictiated.
>
>
>
>
>
>
> On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver  > wrote:
>
>> On 07/10/2015 05:54 AM, Ramesh T wrote:
>>
>>>
>>> select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD
>>> HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset  FROM
>>> pg_catalog.pg_timezone_names
>>> WHERE name='US/Eastern'),'-MM-DD
>>> HH24'||':'||'MI'||':'||'SS')::timestamptz;
>>>
>>> it's not displaying timezone..any help..?
>>>
>>>
>> Forget my previous post, Rays post made me realize the error of my ways,
>> namely thinking current_timestamp-to_timestamp was a function.
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: [GENERAL] xmltable in postgres like in oracle..?

[Ramesh T]

WITH idtable AS (
SELECT '2342,8766' id
)

 select * from inv where id in(
 SELECT id
FROM idtable)

But in query need seperate row by row
like 2342
  8766
using these idtable id i'm checking in inv table id, if same id then return
result from inv table

how to seperate row by row from temp table?

On Sun, Jul 12, 2015 at 5:38 PM, Ramesh T 
wrote:

> xml table is the oracle function ..thank you sir
>
> On Sat, Jul 11, 2015 at 11:04 PM, Adrian Klaver  > wrote:
>
>> On 07/11/2015 08:32 AM, Ramesh T wrote:
>>
>>> Hi,
>>>is xmltable available in postgres..?,if not please give me a
>>> advice to replace the xmtable in postgres..?
>>>
>>
>> What is xmltable?
>>
>> Go to:
>>
>> http://www.postgresql.org/docs/9.4/interactive/index.html
>>
>> and in the Search field type xml
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: [GENERAL] xmltable in postgres like in oracle..?

[Ramesh T]

i got function
select regexp_split_to_table('2,1', E',')::bigint

thanks

On Mon, Jul 13, 2015 at 4:13 PM, Ramesh T 
wrote:

> WITH idtable AS (
> SELECT '2342,8766' id
> )
>
>  select * from inv where id in(
>  SELECT id
> FROM idtable)
>
> But in query need seperate row by row
> like 2342
>   8766
> using these idtable id i'm checking in inv table id, if same id then
> return result from inv table
>
> how to seperate row by row from temp table?
>
> On Sun, Jul 12, 2015 at 5:38 PM, Ramesh T 
> wrote:
>
>> xml table is the oracle function ..thank you sir
>>
>> On Sat, Jul 11, 2015 at 11:04 PM, Adrian Klaver <
>> adrian.kla...@aklaver.com> wrote:
>>
>>> On 07/11/2015 08:32 AM, Ramesh T wrote:
>>>
 Hi,
is xmltable available in postgres..?,if not please give me a
 advice to replace the xmtable in postgres..?

>>>
>>> What is xmltable?
>>>
>>> Go to:
>>>
>>> http://www.postgresql.org/docs/9.4/interactive/index.html
>>>
>>> and in the Search field type xml
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>

Re: [GENERAL] timestamp check

[Adrian Klaver]

On 07/11/2015 11:21 PM, Ramesh T wrote:

postgres query
select current_timestamp-
TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||'
'||(SELECT utc_offset  FROM pg_catalog.pg_timezone_names
WHERE name=DATETIMEZOZE1)   , ''-MM-DD HH24'||' '||'MI'||'
'||'SS')::timestamptz

getting result..


First there is no indication of what DATE1 and DATETIMEZOZE1 are, so the 
result you show has no context.


Second why are you doing all this? You are taking a timestamp (I 
presume) converting it to a string and then converting it back to a 
timestamp, just to to timestamp subtraction.


From what I can piece together you seem to be wanting to get the 
interval between two timestamps, is this correct?


Is so or if not, show us a schematic representation of what you are 
trying to achieve. For example:


'07/13/2015 07:03:15'::timestamp - '01/01/2015 14:15:00'::timestamp

= interval in days, hours, etc



Inline image 1


But in oracle using systimestamp,to_timestamptz and SS TZH is not
supporting  to_timestamp in postgres.

result..

Inline image 2

diffrence is days displaying in postgres query..i thnk something wrong.
is it..?

any help apprictiated.






On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 07/10/2015 05:54 AM, Ramesh T wrote:


select
current_timestamp-to_timestamp(to_char(current_date,'-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset  FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?


Forget my previous post, Rays post made me realize the error of my
ways, namely thinking current_timestamp-to_timestamp was a function.

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





--
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] timestamp check

[Raymond O'Donnell]

On 11/07/2015 17:11, Ramesh T wrote:
> okay,i'm executing a query from pgadmin3.
> 
> i want display time with timezone.But above query  displaying date and
> time not timezone...
> 
> On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston
> mailto:david.g.johns...@gmail.com>> wrote:
> 
> On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T
>  >wrote:
> 
> 
> select
> current_timestamp-to_timestamp(to_char(current_date,'-MM-DD
> HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset  FROM
> pg_catalog.pg_timezone_names  
> WHERE name='US/Eastern'),'-MM-DD
> HH24'||':'||'MI'||':'||'SS')::timestamptz;
> 
> it's not displaying timezone..any help..?

Hi there,

This question was answered a few days ago, but perhaps you didn't see
it. The reason you don't get timezone information is that subtracting
two timestamps results in an interval value. See table 9-27 on this page:

  http://www.postgresql.org/docs/9.4/static/functions-datetime.html

Also, please don't top-post; the convention on this list is to
bottom-post. Thanks! :-)

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

[GENERAL] Obtaining advisory lock using ORDER BY

[Andreas Joseph Krogh]

Hi all.
 
I have this "dequeue" query which is working:
 
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string 
fromorigo_queue_entry qe WHERE qe.queue_id = 2 AND pg_try_advisory_xact_lock(
sequence_id) LIMIT 1 FOR UPDATE ; 
I'm not sure is this is guaranteed to lock in ASC-order on column sequence_id, 
is it?
 
To ensure this I've tried with an explicit ORDER BY on "sequence_id", like 
this:
 
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string 
fromorigo_queue_entry qe WHERE qe.queue_id = 2 AND pg_try_advisory_xact_lock(
sequence_id) ORDER BY qe.sequence_id ASC LIMIT 1 FOR UPDATE ; 

But the latter query results in all non-locked rows being locked (but it 
returns only 1 row due to LIMIT 1), but I'd like the "lowest" non-loced one.
 
Is there a way to make the locking work on an custom ordered set, preserving 
the "LIMIT 1"?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] Obtaining advisory lock using ORDER BY

[Tom Lane]

Andreas Joseph Krogh  writes:
> I have this "dequeue" query which is working:
>  
> select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , 
> qe.payload_string 
> fromorigo_queue_entry qe WHERE qe.queue_id = 2 AND pg_try_advisory_xact_lock(
> sequence_id) LIMIT 1 FOR UPDATE ; 
> I'm not sure is this is guaranteed to lock in ASC-order on column 
> sequence_id, 
> is it?

No.  You could possibly do SELECT...ORDER BY...FOR UPDATE in a
subquery and do the pg_try_advisory_xact_lock call in the outer query.

It might take some fooling around to get a plan that doesn't lock
more rows than necessary; EXPLAIN is your friend.

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] Obtaining advisory lock using ORDER BY

[Andreas Joseph Krogh]

På mandag 13. juli 2015 kl. 16:22:28, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>:
Andreas Joseph Krogh  writes:
 > I have this "dequeue" query which is working:
 >
 > select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , 
qe.payload_string
 > fromorigo_queue_entry qe WHERE qe.queue_id = 2 AND 
pg_try_advisory_xact_lock(
 > sequence_id) LIMIT 1 FOR UPDATE ;
 > I'm not sure is this is guaranteed to lock in ASC-order on column 
sequence_id,
 > is it?

 No.  You could possibly do SELECT...ORDER BY...FOR UPDATE in a
 subquery and do the pg_try_advisory_xact_lock call in the outer query.

 It might take some fooling around to get a plan that doesn't lock
 more rows than necessary; EXPLAIN is your friend.

 regards, tom lane
 
I'm unable to construct such a query.
 
This query blocks and tries to lock the same row (highest sequence_id):
 
SELECT * FROM (SELECT qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id, qe.
payload_string FROM origo_queue_entry qe WHERE qe.queue_id = 2 ORDER BY qe.
sequence_idDESC  LIMIT 1 FOR UPDATE ) q WHERE 
pg_try_advisory_xact_lock(sequence_id) 
 
I'm trying this (on pg-9.4) as an alternative to 9.5's new SKIP LOCKED.
 
Is there a way to accomplish "lock next non-locked row with custom ordering" 
using pg-9.4?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] could not create shared memory segment: Invalid argument

[Ryan King - NOAA Affiliate]

Apologies ahead of time for not knowing which group to send to, but I
wanted to see if anyone has encountered and resolved this type of error.
I'm setting up postgresql 9.2 streaming replication on RH and after copying
the master data directory over to the slave, the psql service refuses start
and gives the following errors.



   2015-07-13 23:55:41.224 UTC FATAL:  could not create shared memory
segment: Invalid argument
   2015-07-13 23:55:41.224 UTC DETAIL:  Failed system call was
shmget(key=5432001, size=1146945536, 03600).
   2015-07-13 23:55:41.224 UTC HINT:  This error usually means that
PostgreSQL's request for a shared memory segment exceeded your kernel's
SHMMAX parameter.  You can either reduce the request size or reconfigure
the kernel with larger SHMMAX.  To reduce the request size (currently
1146945536 bytes), reduce PostgreSQL's shared memory usage, perhaps by
reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less
than your kernel's SHMMIN parameter, in which case raising the request size
or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared
memory configuration.
   2015-07-13 23:56:21.344 UTC FATAL:  could not create shared memory
segment: Invalid argument
   2015-07-13 23:56:21.344 UTC DETAIL:  Failed system call was
shmget(key=5432001, size=58302464, 03600).
   2015-07-13 23:56:21.344 UTC HINT:  This error usually means that
PostgreSQL's request for a shared memory segment exceeded your kernel's
SHMMAX parameter.  You can either reduce the request size or reconfigure
the kernel with larger SHMMAX.  To reduce the request size (currently
58302464 bytes), reduce PostgreSQL's shared memory usage, perhaps by
reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less
than your kernel's SHMMIN parameter, in which case raising the request size
or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared
memory configuration.



I've set shared_buffer way down to next to nothing along with kernel.shmmax
and kernel.shmall per some blogs. However, the same error persists, and I'm
getting no where. I think ultimately the solution is to upgrade, but the
devs may not be ready for an upgrade at this point. Any help would be
greatly appreciated. Thanks!

Re: [GENERAL] [ADMIN] could not create shared memory segment: Invalid argument

[Scott Whitney]

What is your kernel SHMMAX? Usually somewhere under /etc/sysconfig. Depends on 
your distro. This is telling you that your kernel does not have sufficient 
resources.

 Original message 
From: Ryan King - NOAA Affiliate 
Date: 07/13/2015 7:10 PM (GMT-06:00)
To: pgsql-general@postgresql.org, pgsql-ad...@postgresql.org, 
pgsql-performa...@postgresql.org
Subject: Re: [ADMIN] could not create shared memory segment: Invalid argument

Apologies ahead of time for not knowing which group to send to, but I wanted to 
see if anyone has encountered and resolved this type of error. I'm setting up 
postgresql 9.2 streaming replication on RH and after copying the master data 
directory over to the slave, the psql service refuses start and gives the 
following errors.



   2015-07-13 23:55:41.224 UTC FATAL:  could not create shared memory segment: 
Invalid argument
   2015-07-13 23:55:41.224 UTC DETAIL:  Failed system call was 
shmget(key=5432001, size=1146945536, 03600).
   2015-07-13 23:55:41.224 UTC HINT:  This error usually means that 
PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX 
parameter.  You can either reduce the request size or reconfigure the kernel 
with larger SHMMAX.  To reduce the request size (currently 1146945536 bytes), 
reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or 
max_connections.
If the request size is already small, it's possible that it is less 
than your kernel's SHMMIN parameter, in which case raising the request size or 
reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared 
memory configuration.
   2015-07-13 23:56:21.344 UTC FATAL:  could not create shared memory segment: 
Invalid argument
   2015-07-13 23:56:21.344 UTC DETAIL:  Failed system call was 
shmget(key=5432001, size=58302464, 03600).
   2015-07-13 23:56:21.344 UTC HINT:  This error usually means that 
PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX 
parameter.  You can either reduce the request size or reconfigure the kernel 
with larger SHMMAX.  To reduce the request size (currently 58302464 bytes), 
reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or 
max_connections.
If the request size is already small, it's possible that it is less 
than your kernel's SHMMIN parameter, in which case raising the request size or 
reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared 
memory configuration.



I've set shared_buffer way down to next to nothing along with kernel.shmmax and 
kernel.shmall per some blogs. However, the same error persists, and I'm getting 
no where. I think ultimately the solution is to upgrade, but the devs may not 
be ready for an upgrade at this point. Any help would be greatly appreciated. 
Thanks!


Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.
f 512-834-8858

Do you receive our promotional emails? You can subscribe or unsubscribe to 
those emails at http://go.journyx.com/emailPreference/e/4932/714/

Re: [GENERAL] Disconnected but query still running

[Venkata Balaji N]

On Sat, Jul 11, 2015 at 8:53 AM, Eduardo Piombino  wrote:

>
> I've read that that a query still running after a disconnection is
> normal, since the server won't realize about this disconnection until
> it needs to send some data back to the client, which is ok and
> understandable. But my question is, what happens when the query
> finishes? FYI, the query was an update, in what would be autocommit
> mode (no explicit transaction was open). Will the changes be commited?
> or rollbacked because it will detect the disconnection later on?


If the autocommit is on and the transaction is not with-in BEING-END block,
then, the changes should be committed. If the query/process detects the
disconnection later on, it simply hangs which needs to be killed manually
at the database end.


> In
> other words, is it worth waiting for this query to finish (it has been
> running for almost 7 hours now), or should I cancel it because it will
> irremediably be rolled back when the server finds theres no one on the
> other end of the tcp connection?
>

Generally, when the application session is killed abnormally, the query
still continues at the database end. If you want process not to continue
anymore at the database-end, then, you need to manually kill it at the
database end too using pg_cancel_backend() or pg_terminate_backend().

Regards,
Venkata Balaji N

Fujitsu Australia