[GENERAL] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves
One of my postgres backends was killed by the oom-killer. Now, one of my streaming replication slaves is reporting "invalid contrecord length 2190 at A6C/331AAA90" in the logs and replication has paused. I have other streaming replication slaves that are fine. Is that expected? It's happened twice in two days. I'm running 9.3.5 on the master. I have 9.3.4 on the slave that has the problem, and 9.3.5 on the slave that doesn't have the problem. Is this something that was fixed in 9.3.5? The slave that has the problem is also located across the country, while the slave that works is in the same data center as the master -- not sure if that's related at all. Joe
Re: [GENERAL] Finding date intersections
On Sat, Oct 25, 2014 at 5:00 AM, wrote: > John McKown writes: > > > I've been think about this for a bit. But I'm not getting a real > solution. > > I have an approach, shown below, that I think might be the bare > beginnings > > of an approach, but I'm just not getting any more inspiration. Perhaps it > > will spark an idea for you or someone else. > > > > with recursive explode(times) as ( > > select * from sales > > union > > select tstzrange(lower(a.times)::timestamptz,upper(b.times)::timestamptz) > > as times > > from explode as a > > join sales as b > > on upper(a.times) = lower(b.times) > > where lower(a.times) is not null and upper(b.times) is not null > > ) > > select * from explode > > order by times > > ; > > > > If you run it with your example, you will see that it does get rows which > > contain the answer. But it gets all the intermediate rows as well. It is > > removing those "intermediate result" rows that I just can't get a handle > > onl > > For that, you could use a LEFT JOIN with itself: > > WITH RECURSIVE explode(times) AS ( > SELECT times > FROM sales > UNION > SELECT a.times + b.times > FROM explode a > JOIN sales b ON b.times && a.times OR b.times -|- a.times > ) > SELECT a.times > FROM explode a > LEFT JOIN explode b ON b.times @> a.times AND b.times != a.times > WHERE b.times IS NULL > ORDER BY a.times > Perfect! Thanks! Now I just need to understand how that works.. :) Joe
Re: [GENERAL] Finding date intersections
John McKown writes: > I've been think about this for a bit. But I'm not getting a real solution. > I have an approach, shown below, that I think might be the bare beginnings > of an approach, but I'm just not getting any more inspiration. Perhaps it > will spark an idea for you or someone else. > > with recursive explode(times) as ( > select * from sales > union > select tstzrange(lower(a.times)::timestamptz,upper(b.times)::timestamptz) > as times > from explode as a > join sales as b > on upper(a.times) = lower(b.times) > where lower(a.times) is not null and upper(b.times) is not null > ) > select * from explode > order by times > ; > > If you run it with your example, you will see that it does get rows which > contain the answer. But it gets all the intermediate rows as well. It is > removing those "intermediate result" rows that I just can't get a handle > onl For that, you could use a LEFT JOIN with itself: WITH RECURSIVE explode(times) AS ( SELECT times FROM sales UNION SELECT a.times + b.times FROM explode a JOIN sales b ON b.times && a.times OR b.times -|- a.times ) SELECT a.times FROM explode a LEFT JOIN explode b ON b.times @> a.times AND b.times != a.times WHERE b.times IS NULL ORDER BY a.times -- 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] dblink password required
>> >>> select * from dblink(‘dbname=database2 username=db_link >>> password=mypassword','select username, email from appuser') as t1(username >>> text, email text);: > > I think the problem is the above- ^^^ > > username=db_link should be user=db_link > > The accepted keywords are here: > http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING Yes that’s it, thanks. -- 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] Non-capturing expressions
On 25 October 2014 11:49, Francisco Olarte wrote:
> Hi Thom:
>
> On Sat, Oct 25, 2014 at 11:24 AM, Thom Brown wrote:
>
>> It must be that I haven't had enough caffeine today, but I can't figure
>> out why the following expression captures the non-capturing part of the
>> text:
>> # SELECT regexp_matches('postgres','(?:g)r');
>> regexp_matches
>>
>> {gr}
>> (1 row)
>>
>
> Section 9.7.3, search for 'If the pattern contains no parenthesized
> subexpressions, then each row returned is a single-element text array
> containing the substring matching the whole pattern.'
>
Ah, I knew I missed something:
# SELECT regexp_matches('postgres','(?:g)(r)');
regexp_matches
{r}
(1 row)
Although I can see it's redundant in this form.
>
>> I'm expecting '{r}' in the output as I thought this would use ARE mode by
>> default.
>>
>
> Why r ? Your pattern is exactly the same as 'gr'. NOTHING gets captured.
> To get that you'll need the opposite 'g(r)' to capture it. By default
> nothing gets captured, the (?:...) construction is used because () does
> GROUPING and CAPTURING, and sometimes you want grouping WITHOUT capturing.
>
I'm familiar with regular expression syntax, just famliarising myself with
PostgreSQL's syntax flavour.
Thanks
Thom
Re: [GENERAL] Emulating flexible regex replace
Hi: On Fri, Oct 24, 2014 at 6:13 PM, twoflower wrote: > It works very vell. However, I am not completely satisfied with i as it's > unnecessarily loading larger data set than it absolutely must. Besides, I'd > also like to get some experience in DB programming. That's why the PL/Perl > way seems pretty attractive to me. > Try it them. But bear in mind you are not *unnecessarily* loading the data set, the server will need to load it to apply the plperl filters, you will just avoid loading it in the client and transmitting it. You are making a trade off, a design decission. You are trading some network and client cpu usage for some server cpu usage and coding complexity. The tricky part is the the coding complexity, which needs a lot of resources you may never recover. Part of the experience in DB programming is knowing what NOT to do in the database, so coding this will be good, the worst thing that could happen is you do it and then drop it after learning one thing. Francisco Olarte.
[GENERAL] Non-capturing expressions
Hi all,
It must be that I haven't had enough caffeine today, but I can't figure out
why the following expression captures the non-capturing part of the text:
# SELECT regexp_matches('postgres','(?:g)r');
regexp_matches
{gr}
(1 row)
I'm expecting '{r}' in the output as I thought this would use ARE mode by
default.
Thom
