On Sun, Jan 11, 2015 at 8:07 AM, Michael Nolan wrote:
>
>
> On Sat, Jan 10, 2015 at 8:54 PM, Melvin Davidson
> wrote:
>
>> Just curious. Have you checked that the tables are being vacuum/analyzed
>> periodically and that the statistics are up to date? Try running the
>> following query to verify
Hi,
Any estimates on when 9.3.6 will be released? We've been running off
9-3-stable for the past five months, as there's some fixes in there that we
need.
Thanks,
Joe
On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo <
emanuel.ca...@2ndquadrant.com> wrote:
>
> El 23/10/14 a las 17:40, Joe Van Dyk escibió:
> > Hi,
> >
> > I have a master and a slave database.
> >
> > I've got hot_standby_feedback turned on,
> >
On Tue, Oct 28, 2014 at 7:43 AM, Andres Freund
wrote:
> On 2014-10-25 13:55:57 -0700, Joe Van Dyk wrote:
> > 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
bout the oom killer, but rather about
why just one of the slaves is reporting the "invalid contrecord length"
error.
> I hope that's helpful.
>
> Regards,
> basti
>
> On Sat 25.10.2014 22:55 +0200, Joe Van Dyk wrote:
> > One of my postgres backends was killed by
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
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. Pe
On Fri, Oct 24, 2014 at 11:02 AM, David G Johnston <
david.g.johns...@gmail.com> wrote:
> John McKown wrote
> >> insert into sales values
> >> (tstzrange('2014-1-1', '2014-1-2')),
> >> (tstzrange('2014-1-2', '2014-1-3')),
> >> (tstzrange('2014-1-2', '2014-1-4')),
> >> (tstzrange('2014-1-5'
I have a table of sales that have possibly overlapping time ranges. I want
to find all the timeranges where there's an active sale. How would you do
that?
create table sales (
times tstzrange
);
insert into sales values
(tstzrange('2014-1-1', '2014-1-2')),
(tstzrange('2014-1-2', '2014-1-3')
Hi,
I have a master and a slave database.
I've got hot_standby_feedback turned on, max_standby_streaming_delay=-1.
I've configured the master and slave to keep a few days of WALs around.
I've noticed that when some large queries are run on the standby machine
(ones that take more than a minute o
On Tue, Sep 2, 2014 at 9:55 PM, Peter Geoghegan wrote:
> On Tue, Sep 2, 2014 at 9:38 PM, Joe Van Dyk wrote:
> > I want to return all rows that have a value of less than 10. I have
> > arbitrary keys I want to check (not just 'a').
>
>
> If you created an expre
Is it possible to get this query (or a similar one) to use an index?
I want to return all rows that have a value of less than 10. I have
arbitrary keys I want to check (not just 'a').
drop table if exists test;
create table test (j jsonb);
insert into test select json_build_object('a', i)::json
On Tue, Aug 19, 2014 at 3:20 PM, Joe Van Dyk wrote:
> On Tue, Aug 19, 2014 at 3:16 PM, Joe Van Dyk wrote:
>
>> On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk wrote:
>>
>>> I have a large table that I don't want to lock for more than couple
>>> second
On Tue, Aug 19, 2014 at 3:16 PM, Joe Van Dyk wrote:
> On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk wrote:
>
>> I have a large table that I don't want to lock for more than couple
>> seconds. I want to add a nullable column to the table, the type of the
>> co
On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk wrote:
> I have a large table that I don't want to lock for more than couple
> seconds. I want to add a nullable column to the table, the type of the
> column is a domain with a check constraint.
>
> It appears that the check
I have a large table that I don't want to lock for more than couple
seconds. I want to add a nullable column to the table, the type of the
column is a domain with a check constraint.
It appears that the check constraint is being checked for each row, even
though the column can be nullable? Is ther
On Thu, May 22, 2014 at 10:52 AM, Tom Lane wrote:
> Jeff Janes writes:
>> On Wed, May 21, 2014 at 7:48 PM, Joe Van Dyk wrote:
>>> I was expecting that the RI update triggers would have a "when (new.key is
>>> distinct from old.key)" condition on them, wh
On Wednesday, May 21, 2014, Jeff Janes wrote:
>
> On Wed, May 21, 2014 at 1:11 PM, Joe Van Dyk
>
> > wrote:
>
>> I came across http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/
>> which seems to indicate so.
>>
>> When I run the following
I came across http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/
which seems to indicate so.
When I run the following test script, having 50 foreign keys takes
about twice as long to do the update. Is there a reason for that?
Seems like the RI triggers wouldn't have to run on updates if t
Is there a way to add a NOT NULL constraint to a column without having
to lock the table while a sequential read happens?
Seems like it should be possible to add an index on the column for
null values, like:
create index on t using btree(col_name) where col_name is null;
Then when adding the not
On Wed, Apr 2, 2014 at 12:37 PM, Bret Stern <
bret_st...@machinemanagement.com> wrote:
> Any opinions/comments on using SSD drives with postgresql?
>
Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs?
Been looking at
http://aws.amazon.com/about-aws/whats-new/2013/12/19/a
On Thu, Mar 13, 2014 at 12:15 PM, Joe Van Dyk wrote:
> I've got a postgresql 9.3.2 server, compiled from scratch. Getting this
> error:
>
> # select hstore('a', 'b')::json;
> ERROR: cannot cast type hstore to json
I've got a postgresql 9.3.2 server, compiled from scratch. Getting this
error:
# select hstore('a', 'b')::json;
ERROR: cannot cast type hstore to json
LINE 1: select hstore('a', 'b')::json;
^
# select array_to_json(array[hstore('a', 'b')]);
array_to_json
-
I'd like to have join_collapse_limit=20 for all users that belong to a
certain group. Is there a way to do that without having to alter all the
roles that are in that group?
$ psql monkey
psql (9.3.1)
Type "help" for help.
monkey=# create user f1 login;
CREATE ROLE
monkey=# create user f2 in role
On Tue, Jan 7, 2014 at 10:11 AM, ChoonSoo Park wrote:
> Hello Gurus,
>
> I have several tables with lots of boolean columns.
> When I run select query for the tables, I always get 't' or 'f' for
> boolean types.
>
> Is there a way to return 'true' or 'false' string for boolean type except
> using
On Tue, Jan 7, 2014 at 11:47 AM, Susan Cassidy <
susan.cass...@decisionsciencescorp.com> wrote:
> When I start postgres using postgres -D $PGDATA, it hangs, and I see that
> postgres and all the other attendant processes are running, but I never get
> my prompt back.
>
> If I hit ctl/C, postgres e
On Tue, Jan 7, 2014 at 10:41 AM, ChoonSoo Park wrote:
> On Tue, Jan 7, 2014 at 1:29 PM, Szymon Guz wrote:
>
>> On 7 January 2014 19:11, ChoonSoo Park wrote:
>>
>>> Hello Gurus,
>>>
>>> I have several tables with lots of boolean columns.
>>> When I run select query for the tables, I always get '
On Mon, Dec 30, 2013 at 9:11 PM, Sergey Konoplev wrote:
> On Mon, Dec 30, 2013 at 8:56 PM, Joe Van Dyk wrote:
> > On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev
> wrote:
> >> On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk wrote:
> >> > On Sun, Dec 29,
On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev wrote:
> On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk wrote:
> > On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev
> wrote:
> >> On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk wrote:
> >> > On Wed, Dec 18,
On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev wrote:
> On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk wrote:
> > I'm running Postgresql 9.3. I have a streaming replication server.
> Someone
> > was running a long COPY query (8 hours) on the standby which halt
w());
insert into t values (default);
select row_to_json(t) from t;
row_to_json
---
{"id":1,"created_at":"2013-12-23 17:37:08.825935-08"}
On Mon, Dec 23, 2013 at 5:28 PM, Joe Van Dyk wro
ay to easily let
javascript applications parse json timestamps generated by postgresql in
row_to_json() statements.
On Fri, Dec 20, 2013 at 6:27 PM, Joe Van Dyk wrote:
> On Fri, Dec 20, 2013 at 4:24 PM, Joe Van Dyk wrote:
>
>>
>>
>> On Fri, Dec 20, 2013 at 4:18 PM, Joe Va
On Fri, Dec 20, 2013 at 4:24 PM, Joe Van Dyk wrote:
>
>
> On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk wrote:
>
>> # select to_json(now());
>> to_json
>> -
>> "2013-12-20 15:53:39.098204-08"
>> (1
On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk wrote:
> # select to_json(now());
> to_json
> -
> "2013-12-20 15:53:39.098204-08"
> (1 row)
>
> I'd like to see it output "2013-12-20T15:53:39.098204-08&quo
# select to_json(now());
to_json
-
"2013-12-20 15:53:39.098204-08"
(1 row)
I'd like to see it output "2013-12-20T15:53:39.098204-08" so it's
interchangeable with more systems.
http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representation
)
On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk wrote:
> I'm running Postgresql 9.3. I have a streaming replication server. Someone
> was running a long COPY query (8 hours) on the standby which halted
> replication. The replication stopped at 3:30 am. I canceled the
> long-r
I'm running Postgresql 9.3. I have a streaming replication server. Someone
was running a long COPY query (8 hours) on the standby which halted
replication. The replication stopped at 3:30 am. I canceled the
long-running query at 9:30 am and replication data started catching up.
The data up until 1
On Thu, Nov 21, 2013 at 6:11 PM, Tom Lane wrote:
> Joe Van Dyk writes:
> > I had a function that was set to SECURITY INVOKER. I needed to give
> access
> > to a view that uses this function to a role, so I made the function
> > SECURITY DEFINER.
>
> > The
I had a function that was set to SECURITY INVOKER. I needed to give access
to a view that uses this function to a role, so I made the function
SECURITY DEFINER.
The function is STABLE and is usually inlined and takes 2 ms to run.
Immediately, the function quit being inlined and took 1500ms to run
I started getting this error after upgrading from 9.2.4 to 9.3.1:
ERROR: tuple to be updated was already modified by an operation triggered
by the current command
HINT: Consider using an AFTER trigger instead of a BEFORE trigger to
propagate changes to other rows.
STATEMENT: DELETE FROM "channe
On Tue, Sep 3, 2013 at 1:41 PM, Martin Renters wrote:
> I'm trying to use timestamp ranges to keep track of the values particular
> items had over time, but I'm unable to create a table as follows:
>
> test=# create extension btree_gist;
> CREATE EXTENSION
> test=# create table v(item uuid, lifet
Mostly just curious, as this is preventing me from using tab-separated
output. I'd like there to be a header in my files. I have to use CSVs
instead.
Joe
Hi,
Any chance ip4r could be an official postgresql extension? It's got a lot
of advantages over the existing cidr/inet stuff.
https://github.com/RhodiumToad/ip4r-historical/blob/master/README.ip4r
Joe
On Friday, July 5, 2013, Jeff Davis wrote:
> On Tue, 2013-06-11 at 14:05 -0700, Joe Van Dyk wrote:
> > # select tsrange(null)::tstzrange;
> > ERROR: cannot cast type tsrange to tstzrange
> > LINE 1: select tsrange(null)::tstzrange;
> >
> I agree that there should
On Thursday, July 25, 2013, Tim Spencer wrote:
> Hello there!
>
> I've seen lots of people who have asked questions about how to log
> this or that, but I have the opposite question! :-) I'm seeing this in my
> logs:
>
> Jul 25 18:08:11 staging-db11 postgres[27050]: [10-2] STATEMENT: cr
On Thu, Jul 18, 2013 at 2:31 PM, Thomas Kellerer wrote:
> Joe Van Dyk wrote on 18.07.2013 23:23:
>
> Will the custom worker support in 9.3 let me put cron-like tasks into
>> postgresql?
>>
>> I have a lot of database functions that should run every few seconds,
>&g
Will the custom worker support in 9.3 let me put cron-like tasks into
postgresql?
I have a lot of database functions that should run every few seconds, every
minute, every hour, once a week, etc. Right now, I always have to have
exactly one machine setup with cron tasks that tells postgresql to ru
On Tue, Jul 9, 2013 at 4:29 PM, Adrian Klaver wrote:
> On 07/09/2013 04:05 PM, Joe Van Dyk wrote:
>
>> It's looking like I can use a plpgsql function to insert data into a
>> table that violates a domain constraint. Is this a known problem?
>>
>> Session 1:
Check
┼───┼──┼──┼───
public │ my_domain │ text │ │ CHECK (length(VALUE) > 5)
(1 row)
On Tue, Jul 9, 2013 at 4:05 PM, Joe Van Dyk wrote:
> It's looking like I can use a plpgsql function to insert data into a table
> that violates a domain constraint. Is this a known problem?
>
>
It's looking like I can use a plpgsql function to insert data into a table
that violates a domain constraint. Is this a known problem?
Session 1:
create domain my_domain text check (length(value) > 2);
create table my_table (name my_domain);
create function f(text) returns void as $$
declare my_
create domain m numeric(5,2);
create table t (c m);
create function f(t) returns m as $ select case when true then $1.c end $
language sql;
psql:/tmp/t1.sql:3: ERROR: return type mismatch in function declared to
return m
DETAIL: Actual return type is numeric.
CONTEXT: SQL function "f"
Also on 9.3 beta2.
On Thu, Jul 4, 2013 at 5:40 AM, Moshe Jacobson wrote:
> Confirmed reproducible on version 9.1 as well. Very odd.
>
>
> On Wed, Jul 3, 2013 at 1:30 PM, pg noob wrote:
>
>>
>> Hi all,
>>
>> I am trying to understand some odd locking behaviour.
>> I apologize in advance if this
On Thu, Jul 4, 2013 at 4:22 PM, Michael Paquier
wrote:
> On Fri, Jul 5, 2013 at 6:10 AM, Joe Van Dyk wrote:
> > Hi,
> >
> > Is refreshing a materialized view in 9.3 basically:
> >
> > delete from mat_view;
> > insert into mat_view select * from base_v
Hi,
Is refreshing a materialized view in 9.3 basically:
delete from mat_view;
insert into mat_view select * from base_view;
Or is it more efficient? If no rows have changed, will new tuples be
written on a refresh?
Joe
I'd like the execution plan to be in the psql output, not in the postgres
log.
On Tue, Jul 2, 2013 at 11:20 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Joe Van Dyk wrote:
>
> > I frequently need to analyze the last query in psql:
> > sel
g
>
> On Tue, 2 Jul 2013, Joe Van Dyk wrote:
>
> I frequently need to analyze the last query in psql:
>>select * from table where id = 1;
>>explain analyze select * from table where id = 1;
>>
>> It would be nice to be able to do this:
>>ex
I frequently need to analyze the last query in psql:
select * from table where id = 1;
explain analyze select * from table where id = 1;
It would be nice to be able to do this:
explain analyze $LAST
(or can I do something like that already?)
I'm not using psql interactively, I pipe f
Am I doing something silly? Or is the row-estimation for gist indexes not
even close in this case?
https://gist.github.com/joevandyk/503cc3d836ee5d101224/raw/c6fc53b2da06849d3d04effbd1c147fc36124245/gistfile1.txtor
code below:
-- This is not running inside a transaction.
drop table if exists f;
# select tsrange(null)::tstzrange;
ERROR: cannot cast type tsrange to tstzrange
LINE 1: select tsrange(null)::tstzrange;
Is this expected?
select null::timestamp::timestamptz;
works fine.
On Wed, Apr 3, 2013 at 7:09 AM, Shaun Thomas wrote:
> Hey!
>
> So, Packt approached me a few months ago and asked me to put together a
> very basic series of short step-by-step instructions on backing up
> PostgreSQL. The title is "Instant PostgreSQL Backup and Restore How-to."
>
Links for those
On Tue, Apr 2, 2013 at 11:16 AM, Merlin Moncure wrote:
> On Tue, Apr 2, 2013 at 10:34 AM, Joe Van Dyk wrote:
> > On Mon, Apr 1, 2013 at 1:11 PM, Tim Uckun wrote:
> >>
> >>
> >>
> >>
> >> On Tue, Apr 2, 2013 at 8:35 AM, jesusthefrog
&g
On Mon, Apr 1, 2013 at 1:11 PM, Tim Uckun wrote:
>
>
>
> On Tue, Apr 2, 2013 at 8:35 AM, jesusthefrog wrote:
>
>> On the topic of 'natural' versus 'synthetic' primary keys, I am generally
>> in the camp that an extra ID field won't cost you too much, and while one
>> may not need it for a simple
On Mon, Apr 1, 2013 at 8:41 PM, Juan Pablo Cook wrote:
> Hi everyone! I need your help with this problem.
>
> I'm using PostgreSQL *9.2 Server* & the latest jdbc
> driver: postgresql-9.2-1002.jdbc4.jar
>
> I have a many to one relation. I have this piece of code:
>
> con.setAutoCommit(false); //t
begin;
create table f (v numeric);
insert into f values (1), (0.8);
select ceil(v) as v from f group by v;
-- sorta expected the result to be grouped by the column alias,
-- not by the in the table
v
───
1
1
This is the correct behavior, right? To group by the column alias, I'd have
to use "g
On Wed, Mar 13, 2013 at 8:47 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:
> On 03/12/2013 09:05 PM, Perry Smith wrote:
>
>> To all who replied:
>>
>> Thank you. ...
>>
>>
>> I had not seriously considered pg_dump / pg_restore because I assumed it
>> would be fairly slow but I will e
On Mar 12, 2013, at 8:42 AM, Perry Smith wrote:
I tried posting this from Google Groups but I did not see it come through
after an hour so this may be a duplicate message for some.
The current testing technique for things like Ruby On Rails has three
choices but all of the choices will not work
Here's a much smaller self-contained example of the problem:
https://gist.github.com/joevandyk/06e1e26219726f11917e/raw/e9b279c2f2776d5825a6adbb04c7a41201f8cd24/gistfile1.txt
Joe
On Fri, Mar 8, 2013 at 4:17 PM, Joe Van Dyk wrote:
>
> https://gist.github.com/joevandyk/070e4728c4c9f
Oops, fixing link.
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txt
summarizes the problem.
On Fri, Mar 8, 2013 at 4:17 PM, Joe Van Dyk wrote:
>
> https://gist.github.com/joevandyk/070e4728c4c9fe1bf0
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txtsummarizes
the problem.
andres on #postgresql says that making #2 use a faster plan shouldn't be
hard, but he doesn't seem #3 happening.
I was surprised about #2 not being faster, andr
On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz wrote:
> Joe Van Dyk wrote:
> > My assumption was that WITH acted just like subselects, but apparently
> they don't? Using WITH doesn't
> > use the expected index.
>
> Currently WITH acts as an "optimization f
My assumption was that WITH acted just like subselects, but apparently they
don't? Using WITH doesn't use the expected index.
(the below also at:
https://gist.github.com/joevandyk/839413fac7b3bdd32cb3/raw/cec015d16bed7f4e20ab0101b58ae74a1df1cdc2/gistfile1.txt
create view promotion_details1 as (
On Fri, Feb 15, 2013 at 7:43 AM, Tom Lane wrote:
> Joe Van Dyk writes:
> > Perhaps I fat-fingered something somewhere... I tried that and I got
> this:
> >
> https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt
>
> Try w
On Thu, Feb 14, 2013 at 6:31 PM, Jack Christensen
wrote:
> Joe Van Dyk wrote:
>
>> See https://gist.github.com/**joevandyk/4957646/raw/**
>> 86d55472ff8b5a4a6740d9c673d18a**7005738467/gistfile1.txt<https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18
See
https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txtfor
the code.
I have promotions(id, end_at, quantity) and promotion_usages(promotion_id).
I have a couple of things I typically want to retrieve, and I'd like those
things to be composable. In
Hi,
I have an index on a column that can be nullable. I decide the column
shouldn't be nullable anymore. So I alter the column to be not
nullable.
That "alter column" query does a full table scan, which can be painful
for large tables. Couldn't that index be used instead?
Thanks,
Joe
--
Sent
http://www.postgresql.org/docs/current/static/continuous-archiving.htmldoesn't
mention pg_receivexlog.
But http://www.postgresql.org/docs/current/static/app-pgreceivexlog.htmlsays
pg_receivexlog can be used for PITR backups.
Should the PITR page reference pg_receivexlog?
I'm running into this bug fixed a few days after 9.2.1 was released:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d2292f6405670e1fdac13998f87b4348c71fb9e6
Anyone know when 9.2.2 will go out?
Thanks,
Joe
Instead of this:
create function some_trigger() returns trigger as $$
begin
if TG_OP = 'DELETE' then
insert into audits values (OLD.value);
else
insert into audits values (NEW.value);
end if;
return NULL;
end
$$ language plpgsql;
create trigger some_trigger after insert on products
fo
Say I want output similar to this:
{
"id":73,
"name":"LolShirt 1",
"uuid":"afe3526818",
"thumbnails":[
{
"filename":"file.png",
"width":200,
"height":199,
"id":79
},
{
"filename":"file.png",
"width":200,
"h
On Sat, Jun 23, 2012 at 3:03 PM, Joe Van Dyk wrote:
> How can I use row_to_json for a subset of columns in a row? (without
> creating a new view or using a CTE?)
>
> What I want returned:
> {"email_address":"j...@tanga.com","username":"joevandyk&q
How can I use row_to_json for a subset of columns in a row? (without
creating a new view or using a CTE?)
What I want returned:
{"email_address":"j...@tanga.com","username":"joevandyk"}
Note that there is no "id" column in the result.
create table users (id serial primary key, email_address varc
81 matches
Mail list logo
