Re: [GENERAL] session_replication_role `replica` behavior

Point taken, thanx, however from the docs, it is far from explicit that setting session_replication_role to 'replica' can disable FK constraints (RI) and finally result in an incosistent database. It might be that RI in postgres is implemented via triggers, but to the user, that is just an implem

Re: [GENERAL] Replication terminated due to PANIC

Sorry my bad , didn't mention the full DB version : 9.2.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit Apart from these i am happy to inform , the issue is fixed now. Actually there are two Slave set up's on the standby box on different ports and

[GENERAL] How to find current row number relative to window frame

Hello, I have a user defined aggregate function and in Sfunc I need to reference current row number relative to window frame. Ideallly I would like to have following construct: select my_aggregate(x,current_row_number_relative_to window) over (order by y rows between n preceding and current row)

[GENERAL] Open transaction with 'idle' (not 'idle in transaction') status

Hi, PostgreSQL 9.2.2, Ubuntu 11.10, Linux 3.0.0. A couple of days ago I noticed a strange output from a cron command I use to terminate long transactions. psql --no-psqlrc --single-transaction -d postgres -t -c "SELECT pg_terminate_backend(pid),now(),now()-xact_start as duration,* from pg_stat_a

Re: [GENERAL] is there a way to deliver an array over column from a query window?

On Thu, Apr 25, 2013 at 1:30 PM, Rafał Pietrak wrote: > W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze: > > On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak > wrote: > > W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze: > > W dniu 03/24/2013 12:06 PM, Misa Simic pisze: > > maybe, > > SELECT DISTIN

Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?

"Carlo Stonebanks" writes: > Ok, I tried to be clever and I wrote code to avoid inserting duplicate data. > The calling function has a try-catch to recover from this, but I am curious > as to why it failed: There's nothing obviously wrong with that, which means the issue is in something you didn'

[GENERAL] Simple SQL INSERT to avoid duplication failed: why?

Ok, I tried to be clever and I wrote code to avoid inserting duplicate data. The calling function has a try-catch to recover from this, but I am curious as to why it failed: INSERT INTO mdx_lib.acache_mdx_logic_address_validation ( address, postal_code, address_id

Re: [GENERAL] is there a way to deliver an array over column from a query window?

W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze: On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak wrote: W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze: W dniu 03/24/2013 12:06 PM, Misa Simic pisze: maybe, SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by invoice_nr) fro

[GENERAL] apt.postgresql.org broken dependency?

Just tried upgrading and added the apt-postgresql.org repo to my Debian server (on testing now) and I got some backages like barman retained because some dependencies couldn't be satisfied. Los siguientes paquetes tienen dependencias incumplidas: barman : Depende: python (< 2.7) pero 2.7.3-4 va a

Re: [GENERAL] session_replication_role `replica` behavior

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 manos tsahakis wrote: > In our application we are enabling session_replication_role TO 'replica' in > certain situations so that triggers will not fire in a table during DML > operations. However, we observed that when setting session_replicatio

Re: [GENERAL] regex help wanted

Karsten Hilbert writes: > I would have thought "<[^<]+?:" should mean: > match a "<" > followed by 1-n characters as long as they are not "<" > until the VERY NEXT ":" > The "?" should make the "+" after "[^<]" non-greedy and thus > stop at the first occurrence of ":", right ?

Re: [GENERAL] regex help wanted

On Thu, Apr 25, 2013 at 03:40:51PM +0100, Thom Brown wrote: > On 25 April 2013 15:32, Tom Lane wrote: > > Karsten Hilbert writes: > >> What I don't understand is: Why does the following return a > >> substring ? > > > >> select substring ('junk $$ junk' from > >> '\$<[^<]+?::[^:]+?>\$');

Re: [GENERAL] regex help wanted

On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote: > Karsten Hilbert writes: > > What I don't understand is: Why does the following return a > > substring ? > > > select substring ('junk $$ junk' from > > '\$<[^<]+?::[^:]+?>\$'); > > There's a perfectly valid match in which [^<]+? m

Re: [GENERAL] regex help wanted

On 25 April 2013 15:32, Tom Lane wrote: > Karsten Hilbert writes: >> What I don't understand is: Why does the following return a >> substring ? > >> select substring ('junk $$ junk' from >> '\$<[^<]+?::[^:]+?>\$'); > > There's a perfectly valid match in which [^<]+? matches allergy::test >

Re: [GENERAL] regex help wanted

Karsten Hilbert writes: > What I don't understand is: Why does the following return a > substring ? > select substring ('junk $$ junk' from > '\$<[^<]+?::[^:]+?>\$'); There's a perfectly valid match in which [^<]+? matches allergy::test and [^:]+? matches 99. rega

Re: [GENERAL] Replication terminated due to PANIC

On 2013-04-24 19:44:25 -0700, Sergey Konoplev wrote: > On Wed, Apr 24, 2013 at 5:05 PM, Adarsh Sharma wrote: > > I have a Postgresql 9.2 instance running on a CentOS6.3 box.Yesterday i > > setup a hot standby by using pgbasebackup. Today i got the below alert from > > standby box : > > > > [1] (f

[GENERAL] regex help wanted

Hi, I am in the process of converting some TEXT data which I try to identify by regular expression. What I don't understand is: Why does the following return a substring ? select substring ('junk $$ junk' from '\$<[^<]+?::[^:]+?>\$'); I would have thought the '::[^:]+?>' part should ha

Re: [GENERAL] pgdump error "Could not open file pg_clog/0B8E: No such file or directory"

On Thu, Apr 25, 2013 at 5:13 AM, wrote: > Sorry for the delay. > No it does not exist. > > ls -l /var/lib/pgsql/data/pg_clog/0B8E > ls: /var/lib/pgsql/data/pg_clog/0B8E: No such file or directory > > Have 92 files in directory which are all 262144 in size. This file records if transactions commi

Re: [GENERAL] Replication terminated due to PANIC

If its really index corruption, then you should be able to fix it by reindexing. However, that doesn't explain what caused the corruption. Perhaps your hardware is bad in some way? On Wed, Apr 24, 2013 at 10:46 PM, Adarsh Sharma wrote: > Thanks Sergey for such a quick response, but i dont think

Re: [GENERAL] is there a way to deliver an array over column from a query window?

On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak wrote: > W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze: > > W dniu 03/24/2013 12:06 PM, Misa Simic pisze: > > maybe, > > SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by > invoice_nr) from invoices; > > > RIGHT. Thenx. (and the

Re: [GENERAL] Set Returning Functions and array_agg()

On Wed, Apr 24, 2013 at 4:26 PM, Stephen Scheck wrote: > Possibly due to my lack of thorough SQL understanding. Perhaps there's a > better way of doing what I'm ultimately trying to accomplish, but still the > question remains - why does this work: > > pg_dev=# select unnest(array[1,2,3]); > unne

Re: [GENERAL] is there a way to deliver an array over column from a query window?

SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ) FROM testy k where k.e <> 'email' and k.c='1035049' ORDER BY a, b, c, e If doesnt work - Probably there is a better option... In worst case I would do SELECT DISTINCT a, b, c, array_agg(d) OVER (PARTITION BY c ) FROM ( SELECT

Re: [GENERAL] custom session variables?

On Thu, Apr 25, 2013 at 1:17 AM, Darren Duncan wrote: > On 2013.04.24 7:16 PM, � wrote: > >> Maybe you must see this extension [1] ;-) >> >> [1] >> http://pgxn.org/dist/session_**variables/ >> >> Fabrízio de Royes Mello >> > > Thanks for your response. > >

Re: [GENERAL] Set Returning Functions and array_agg()

On 2013-04-24, Stephen Scheck wrote: > --f46d043c810aa794a404db21f464 > Content-Type: text/plain; charset=ISO-8859-1 > > Possibly due to my lack of thorough SQL understanding. Perhaps there's a > better way of doing what I'm ultimately trying to accomplish, but still the > question remains - why d

Re: [GENERAL] pgdump error "Could not open file pg_clog/0B8E: No such file or directory"

Sorry for the delay. No it does not exist. ls -l /var/lib/pgsql/data/pg_clog/0B8E ls: /var/lib/pgsql/data/pg_clog/0B8E: No such file or directory Have 92 files in directory which are all 262144 in size. On Wed, Apr 24, 2013 at 9:23 AM, Adrian Klaver wrote: > On 04/24/2013 03:35 AM, jesse.wat.