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
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
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
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 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
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: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
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, 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
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
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')
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'
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
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
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
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
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,
> >
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 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
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
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
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
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 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
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
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
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
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
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
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 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
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 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 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
# 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.
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;
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
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'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
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
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
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
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"
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_
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?
>
>
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:
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 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
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 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
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
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
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
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 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'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 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
# 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 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
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
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
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
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
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 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 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 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: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
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
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
-
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
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
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
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
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
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
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
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
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?
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
81 matches
Mail list logo
