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 adrian.kla...@gmail.com wrote: On 04/24/2013 03:35 AM, jesse.wat...@gmail.com wrote: I am receiving an error when running a pg_dump. These are older legacy systems and upgrading them is not in plan. Any help will be appreciated. CentOS 5.3 (64bit) psql (PostgreSQL) 8.3.11 full error message: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not access status of transaction 3101708884 DETAIL: Could not open file pg_clog/0B8E: No such file or directory. Does the above file actually exist in the pg_clog directory? pg_dump: The command was: COPY blob_store.blobs (blob_id, mime_type, binary_data, create_ts) TO stdout; Command used, pretty standard pg_dump -Fc dbname -f outfile TIA, Jesse Waters -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Set Returning Functions and array_agg()
On 2013-04-24, Stephen Scheck singularsyn...@gmail.com 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 does this work: pg_dev=# select unnest(array[1,2,3]); unnest 1 2 3 (3 rows) But not this: pg_dev=# select array_agg(unnest(array[1,2,3])); ERROR: set-valued function called in context that cannot accept a set the parser doesn't understand it for the reason given same as it doesn't understand this. select avg(generate_series(1,3)); but it does understand this: select avg(a) from generate_series(1,3) as s(a); and this: select array_agg(i) from unnest(array[1,2,3])) as u(i); -- ⚂⚃ 100% natural -- 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] custom session variables?
On Thu, Apr 25, 2013 at 1:17 AM, Darren Duncan dar...@darrenduncan.netwrote:
On 2013.04.24 7:16 PM, � wrote:
Maybe you must see this extension [1] ;-)
[1]
http://pgxn.org/dist/session_**variables/http://pgxn.org/dist/session_variables/
Fabrízio de Royes Mello
Thanks for your response.
/*
* Author: Fabrízio de Royes Mello
* Created at: Thu Oct 27 14:37:36 -0200 2011
*
*/
CREATE FUNCTION set_value(TEXT, TEXT) RETURNS void AS $$
BEGIN
PERFORM set_config('session_variables.**'||$1, $2, false);
RETURN;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION set_value(TEXT, TEXT) IS
'Create/Assign value to a new/existing session variable';
SET check_function_bodies TO OFF;
CREATE FUNCTION get_value(TEXT) RETURNS TEXT AS $$
SELECT current_setting('session_**variables.'||$1);
$$ LANGUAGE sql;
COMMENT ON FUNCTION get_value(TEXT) IS
'Returns the value of session variable passed as a parameter';
So, ok, basically the same as http://frefo.blogspot.ca/2009/**
04/session-variables-in-**postgresql.htmlhttp://frefo.blogspot.ca/2009/04/session-variables-in-postgresql.htmlwhich
I initially pointed to.
I'll take that as several people endorsing set_config()/current_setting()
as a preferred way to do this.
I use this feature to store global session variables for a long time... In
my first implementation of this feature I used temp tables, but this caused
catalog bloat. So I had to change this strategy using
set_config/current_setting functions and it has worked fine since then.
The main limitation seems to be that those builtins just store and return
text values, but a little casting on store/fetch should take care of that.
The temporary table approach wouldn't need casting in contrast.
To solve this you can extend this extension... ;-)
CREATE FUNCTION get_value_as_integer(TEXT) RETURNS INTEGER AS $$
SELECT CAST(get_value($1) AS INTEGER);
$$ LANGUAGE sql;
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
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 a, b, c, d FROM testy where e 'email' and c='1035049' ORDER BY
a, b, c, e
)
Kind Regards,
Misa
2013/4/24 Rafał Pietrak ra...@zorro.isa-geek.com
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 first thing I did, I've read the doc on
array_agg() what stress makes from people :(
Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many duplicates that cause
it). The problem is, that:
--
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' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---
is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY
k.e) FROM testy k where k.e 'email' and k.c='1035049' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {5951948640868}
1035 | 10410053 | 1035049 | {5951948640868,9902031328529}
(2 rows)
--
And this is not at all what I've expected - the aggerate function returned
different values over the selected partition.
I understand, that this behavior (of changing the aggregate function
return values) is there for the purpose of having sum() - and the like -
aggregate functions return accumulating/averaged/etc values as of the
example in postgres documentation (
http://www.postgresql.org/docs/9.1/static/tutorial-window.html)
But the array_agg() is significantly different from other aggregate
functions - it maintains all the trasspassed values within; under such
circumstances: is it reasonable to copy that functionality (of PARTITION
OVER ... ORDER BY...) in it?
A particular value relevant to a particular row (when SELECT withiout
DISTINCT) can be retrieved by RANK() function used as an index into the
resulting array.
But, if (unfortunately) this functionality have to stay: Can somebody pls
help me cooking an SQL that returns the same value of array_agg() over the
entire partition, while letting me control the order of aggregated values,
based on the order of column E?
My table for the showcase was:
---
SELECT * FROM testy;
a |b |c| d | e
--+--+-+--+---
1035 | 10410053 | 1035049 | 9902031328529| tel
1035 | 10410053 | 1035049 | 5291286...@gmail.com | email
1035 | 10410053 | 1035049 | 5951948640868| tel2
(3 rows)
--
thx
-R
Re: [GENERAL] Set Returning Functions and array_agg()
On Wed, Apr 24, 2013 at 4:26 PM, Stephen Scheck singularsyn...@gmail.com 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]); unnest 1 2 3 (3 rows) But not this: pg_dev=# select array_agg(unnest(array[1,2,3])); ERROR: set-valued function called in context that cannot accept a set The solution to the problem is actually of less interest right now then in understanding what's going on in the two statements above. It seems a bit inconsistent to me. If an aggregate function cannot handle rows generated in the columns-part of the statement, then why is a single-column row(s) result acceptable in the first statement? you can do it like this though: select array(select unnest(array[1,2,3])); merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 ra...@zorro.isa-geek.com 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 first thing I did, I've read the doc on
array_agg() what stress makes from people :(
Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many duplicates that cause it).
The problem is, that:
--
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' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---
is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
FROM testy k where k.e 'email' and k.c='1035049' ;
you are aware of in-aggregate ordering (not completely sure if it
meets your use case?
select array_agg(v order by v desc) from generate_series(1,3) v;
also, 'distinct'
select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 eddy.ada...@gmail.com wrote: Thanks Sergey for such a quick response, but i dont think this is some patch problem because we have other DB servers also running fine on same version and message is also different : host= PANIC: _bt_restore_page: cannot add item to page And the whole day replication is working fine but at midnight when log rotates it shows belows msg : 2013-04-24 00:00:00 UTC [26989]: [4945032-1] user= db= host= LOG: checkpoint starting: time 2013-04-24 00:00:00 UTC [26989]: [4945033-1] user= db= host= ERROR: could not open file global/14078: No such file or directory 2013-04-24 00:00:00 UTC [26989]: [4945034-1] user= db= host= CONTEXT: writing block 0 of relation global/14078 2013-04-24 00:00:00 UTC [26989]: [4945035-1] user= db= host= WARNING: could not write block 0 of global/14078 2013-04-24 00:00:00 UTC [26989]: [4945036-1] user= db= host= DETAIL: Multiple failures --- write error might be permanent. Looks like some index corruption. Thanks On Thu, Apr 25, 2013 at 8:14 AM, Sergey Konoplev gray...@gmail.com wrote: On Wed, Apr 24, 2013 at 5:05 PM, Adarsh Sharma eddy.ada...@gmail.com 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] (from line 412,723) 2013-04-24 23:07:18 UTC [13445]: [6-1] user= db= host= PANIC: _bt_restore_page: cannot add item to page When i check, the replication is terminated due to slave DB shutdown. From the logs i can see below messages :- I am not sure that it is your situation but take a look at this thread: http://www.postgresql.org/message-id/CAL_0b1t=WuM6roO8dki=w8dhh8p8whhohbpjreymmqurocn...@mail.gmail.com There is a patch by Andres Freund in the end of the discussion. Three weeks have passed after I installed the patched version and it looks like the patch fixed my issue. 2013-04-24 23:17:16 UTC [26989]: [5360083-1] user= db= host= ERROR: could not open file global/14078: No such file or directory 2013-04-24 23:17:16 UTC [26989]: [5360084-1] user= db= host= CONTEXT: writing block 0 of relation global/14078 2013-04-24 23:17:16 UTC [26989]: [5360085-1] user= db= host= WARNING: could not write block 0 of global/14078 2013-04-24 23:17:16 UTC [26989]: [5360086-1] user= db= host= DETAIL: Multiple failures --- write error might be permanent. I checked in global directory of master, the directory 14078 doesn't exist. Anyone has faced above issue ? Thanks -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- ~ L. Friedmannetll...@gmail.com LlamaLand https://netllama.linux-sxs.org -- 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] pgdump error Could not open file pg_clog/0B8E: No such file or directory
On Thu, Apr 25, 2013 at 5:13 AM, jesse.wat...@gmail.com 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 committed or were aborted. If it is missing, this signifies corruption. You can force a file in, but chances are things will not be as they should be. When is your last good backup? If it's fairly recent you can force a file with all 'commit' values and compare vs backup and attempt to repair any damage if things don't look too bad I'd start with 'reindex' and/or full database restore assuming you could pull a dump). Take a full filesystem backup before doing anything though. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 $allergy::test::99$ junk' from
'\$[^]+?::[^:]+?\$');
I would have thought the '::[^:]+?' part should have meant
after two :s
match at least one character
except any further :s
until the next
I don't find the flaw in my thinking. Can anyone help ?
(Sure, it is not PostgreSQL-specific yet I need to run this
in PostgreSQL on data migration.)
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
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] 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 eddy.ada...@gmail.com 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] (from line 412,723) 2013-04-24 23:07:18 UTC [13445]: [6-1] user= db= host= PANIC: _bt_restore_page: cannot add item to page When i check, the replication is terminated due to slave DB shutdown. From the logs i can see below messages :- Does the global/14078 file exist on the primary? What exact commandline were you using to restore? Which exact version of postgres? I am not sure that it is your situation but take a look at this thread: http://www.postgresql.org/message-id/CAL_0b1t=WuM6roO8dki=w8dhh8p8whhohbpjreymmqurocn...@mail.gmail.com There is a patch by Andres Freund in the end of the discussion. The issues don't look related. Three weeks have passed after I installed the patched version and it looks like the patch fixed my issue. Oh, cool! Thanks for verifying. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] regex help wanted
Karsten Hilbert karsten.hilb...@gmx.net writes:
What I don't understand is: Why does the following return a
substring ?
select substring ('junk $allergy::test::99$ junk' from
'\$[^]+?::[^:]+?\$');
There's a perfectly valid match in which [^]+? matches allergy::test
and [^:]+? matches 99.
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] regex help wanted
On 25 April 2013 15:32, Tom Lane t...@sss.pgh.pa.us wrote:
Karsten Hilbert karsten.hilb...@gmx.net writes:
What I don't understand is: Why does the following return a
substring ?
select substring ('junk $allergy::test::99$ junk' from
'\$[^]+?::[^:]+?\$');
There's a perfectly valid match in which [^]+? matches allergy::test
and [^:]+? matches 99.
Yeah, I think there may be an assumption that a lazy quantifier will
stop short and cause the remainder to fail to match permanently, but
it will backtrack, forcing the lazy quantifier to expand until it can
match the expression.
--
Thom
--
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] regex help wanted
On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote:
Karsten Hilbert karsten.hilb...@gmx.net writes:
What I don't understand is: Why does the following return a
substring ?
select substring ('junk $allergy::test::99$ junk' from
'\$[^]+?::[^:]+?\$');
There's a perfectly valid match in which [^]+? matches allergy::test
and [^:]+? matches 99.
Tom, thanks for helping !
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 ? Or am I
misunderstanding that part ?
At any rate,
select substring ('junk $allergy::test::99$ junk' from
'\$[^:]+?::[^:]+?\$');
(which follows from your hint) appears to do what I need.
Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
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] regex help wanted
On Thu, Apr 25, 2013 at 03:40:51PM +0100, Thom Brown wrote:
On 25 April 2013 15:32, Tom Lane t...@sss.pgh.pa.us wrote:
Karsten Hilbert karsten.hilb...@gmx.net writes:
What I don't understand is: Why does the following return a
substring ?
select substring ('junk $allergy::test::99$ junk' from
'\$[^]+?::[^:]+?\$');
There's a perfectly valid match in which [^]+? matches allergy::test
and [^:]+? matches 99.
Yeah, I think there may be an assumption that a lazy quantifier will
stop short and cause the remainder to fail to match permanently, but
it will backtrack, forcing the lazy quantifier to expand until it can
match the expression.
Yup, therein lies the rub :-)
Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
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] regex help wanted
Karsten Hilbert karsten.hilb...@gmx.net 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 ? Or am I misunderstanding that part ? No, non-greedy just means that if there are multiple ways to make the pattern match the string, prefer the way that makes this sub-match the shortest (whereas the default makes leftmost sub-matches longest). If you don't want the char class to match : then you need to say that explicitly. BTW, I'm fairly sure that unless you are doing something that extracts or replaces sub-matches, there is no value whatever in marking quantifiers non-greedy; that just complicates life for the regex compiler. A match is a match, if you're not paying attention to where the subpattern boundaries are. 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] 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_replication_role TO 'replica' referential integrity constraints will not fire on a table either. ... Shouldn't non-user triggers *not* be affected by session_replication_role ? No. The design of session_replication_role was to enable quick disabling of *all* triggers and rules, including system ones. When you enter that mode, it is assumed that you know what you are doing enough to not create an inconsistency. With Slony and Bucardo, for example, all tables affected by the triggers (e.g. a cascaded delete from a FK) are changed together. 2. Is there any way to just find the name of the FK constraint trigger and convert it to ENABLE ALWAYS? I think you are approaching this in the wrong way. If you want the constraint triggers to fire, but not other user triggers, your best bet is to do: ALTER TABLE foo DISABLE TRIGGER USER; This has a heavy table locking cost, but does exactly what you want: disables all non-system/FK triggers. Your next best bet is probably to emulate the effects of the FK trigger yourself, e.g. deleting from the child table while in 'replica' mode. A further option may be to give your user functions some brains, such that they will not execute when session_replication_role is set to 'local', for example. While I do think session_replication_role needs some more granularity, it's also a little hard to say more without knowing your exact requirements. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201304251145 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlF5UHAACgkQvJuQZxSWSsjm+ACeOT2v7EF90tFr7K892UxIAqnl WpwAoKPkIMC7HTTtvOMj/XbtOVGXe0Fl =2bjH -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 ser instalado Depende: python-argcomplete pero no va a instalarse # apt-cache policy python-argcomplete python-argcomplete: Instalados: (ninguno) Candidato: 0.3.3-1.pgdg60+1 Tabla de versión: 0.3.3-1.pgdg60+1 0 500 http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg/main i386 Packages 0.3.3-1 0 50 http://ftp.de.debian.org/debian/ unstable/main i386 Packages Since when 2.7.3 isn't larger then 2.7. If I use the Debian packages, everything installs without a problem. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] 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 ra...@zorro.isa-geek.com 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 first thing I did, I've read the doc on
array_agg() what stress makes from people :(
Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many duplicates that cause it).
The problem is, that:
--
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' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---
is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
FROM testy k where k.e 'email' and k.c='1035049' ;
you are aware of in-aggregate ordering (not completely sure if it
meets your use case?
select array_agg(v order by v desc) from generate_series(1,3) v;
also, 'distinct'
select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
No, I don't (manual:
http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have
just one word distinct on that page, and it's not in the above
context). And I cannot duplicate the above:
# select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
ERROR: syntax error at or near order
LINE 1: select array_agg(distinct v order by v desc) from (select ge...
Did I miss something??
In the mean time, I was working towards:
# with ktkt(b,l,s,t) as (SELECT a, b, c, array_agg(k.d)OVER (PARTITION
BY k.c ORDER BY k.e) FROM testy k where k.e 'email') select
distinct on (b,l,s) b,l,s,t from ktkt k where k.s='1035049' order by
b,l,s,array_length(t,1) desc;
b |l |s| t
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
(1 row)
---
Which gives the expected result, not exactly, because:
my final goal is to select one contact information for an entity,
which is, say: telephone, and which is, say: mobile. Taking into
account, that the main contact information table is roughly: CREATE
TABLE testy (id_a, id_b, id_c, conact_value, contact_kind, primary key
(id_a, id_b,id_c)). ... I cannot collapse the multiple identification
columns - they collectively form a unique ID, of an entity. That main
contact information table has associated tables like to fixed/mobile
(testy has additional FK columns for that).
And the above partial result isn't working towards my final goal.
But, while writing this response, It occured to me, that, may be I
shouldn't build the array so early in the query, but start with a wider
join (only reduced by the desired contact attributes) . OK. I'll do
some testing with that.
Still, I'll be greatfull for some explanations why the distinct
disdn't work for me. May be that would be a tool for this case.
thnx,
-R
[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 ) SELECT '306 station 22 1 2 st' AS address, '29482' AS postal_code, 100165016 AS address_id WHERE NOT EXISTS ( SELECT 1 FROM mdx_lib.acache_mdx_logic_address_validation WHERE address = '306 station 22 1 2 st' AND postal_code = '29482' ) Exec status=PGRES_FATAL_ERROR error=ERROR: duplicate key value violates unique constraint uq_acache_mdx_logic_address_validation_idx DETAIL: Key (address, postal_code)=(306 station 22 1 2 st, 29482) already exists. The client insists that this process is the only one running, so if he's right no other process could be inserting a row with the same data between the SELECT . NOT EXISTS and the actual INSERT operation. This particular code works as expected right now (SELECT returns 0 rows, therefore no rows INSERTed). Should this have worked? Carlo
Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?
Carlo Stonebanks stonec.regis...@sympatico.ca 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't show us. Care to assemble a self-contained 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
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 ra...@zorro.isa-geek.com wrote:
W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze:
On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak ra...@zorro.isa-geek.com
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 first thing I did, I've read the doc on
array_agg() what stress makes from people :(
Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many duplicates that cause it).
The problem is, that:
--
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' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---
is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
FROM testy k where k.e 'email' and k.c='1035049' ;
you are aware of in-aggregate ordering (not completely sure if it
meets your use case?
select array_agg(v order by v desc) from generate_series(1,3) v;
also, 'distinct'
select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
No, I don't (manual:
http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just
one word distinct on that page, and it's not in the above context). And I
cannot duplicate the above:
# select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
ERROR: syntax error at or near order
LINE 1: select array_agg(distinct v order by v desc) from (select ge...
Did I miss something??
This feature was added w/9.0. This means you are on 8.4. Time to upgrade...
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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_activity where (now() - pg_stat_activity.xact_start) '60
min'::interval and usename NOT IN ('postgres', 'slony', 'backuper') |
grep -v '^$'
t| 2013-04-22 17:50:01.452166+04 |
01:00:41.024359 | 16402 | sports | 21945 | 57857517 |
push_io_notifications.app | | 127.0.0.1 |
| 44784 | 2013-04-22 16:49:20.417845+04 | 2013-04-22
16:49:20.427807+04 | 2013-04-22 16:49:20.427807+04 | 2013-04-22
16:49:20.427838+04 | f | idle | LISTEN fb_marker_insert;
Everything is fine here except the status of the process. It is idle
despite xact_start was not null. I expected it should always be idle
in transaction in such cases.
Are there any exceptions from this rule? May be something connected with LISTEN?
--
Kind regards,
Sergey Konoplev
Database and Software Consultant
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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) as. from …. Any ideas how I can do this. Thanks Art. -- 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] 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 are two stale processes ( logger and writer ) that are running with different parent id's on the box. After killing the processes and reloading conf file, db server is replaying logs properly. @Andres : No the directory doesn't exist on master but exists on the other standby. @Lonni , i was guessing because of the below message in the logs:- _bt_restore_page: cannot add item to page http://en.verysource.com/code/5191515_1/nbtxlog.c.html Yes we faced H/w issues in master and we flip to slave and setup a new SR in which we are facing this issue. Still don't know why this PANIC message came. Anywaz thanks u all for giving your crucial time into it. Thanks On Thu, Apr 25, 2013 at 7:46 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-04-24 19:44:25 -0700, Sergey Konoplev wrote: On Wed, Apr 24, 2013 at 5:05 PM, Adarsh Sharma eddy.ada...@gmail.com 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] (from line 412,723) 2013-04-24 23:07:18 UTC [13445]: [6-1] user= db= host= PANIC: _bt_restore_page: cannot add item to page When i check, the replication is terminated due to slave DB shutdown. From the logs i can see below messages :- Does the global/14078 file exist on the primary? What exact commandline were you using to restore? Which exact version of postgres? I am not sure that it is your situation but take a look at this thread: http://www.postgresql.org/message-id/CAL_0b1t=WuM6roO8dki=w8dhh8p8whhohbpjreymmqurocn...@mail.gmail.com There is a patch by Andres Freund in the end of the discussion. The issues don't look related. Three weeks have passed after I installed the patched version and it looks like the patch fixed my issue. Oh, cool! Thanks for verifying. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
