Re: [GENERAL] How to analyze a slowdown in 9.3.5?

[Joe Van Dyk]

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:
>>
>>
> A vacuum analyze runs every night and there would not have been many
> inserts or updates to the tables used by the lookup function since the
> latest vacuum analyze.  I think I may have even done a vacuum analyze on
> the two largest tables after the first DB shutdown.
>

One thing to check (I ran into this two weeks ago) -- even though vacuums
were happening, a query running on a standby machine was preventing the
vacuum process from removing the dead rows. You may want to check for bloat
or use 'vacuum verbose' to see if there's many dead rows not being cleaned
up.

Joe

[GENERAL] 9.3.6 release?

[Joe Van Dyk]

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

Re: [GENERAL] some queries on standby preventing replication updates

[Joe Van Dyk]

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,
> > 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 or so), replication updates
> > are paused. Is there a way to fix this?
> >
> You may need to set a value on max_standby_streaming_delay, which
> controls the time
> before cancelling the standby queries when a conflict occurs on a
> wal-records-about-to-be-applied.
>
> Source:
> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html


I'm using -1 for that option, would using something different be better?

Re: [GENERAL] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves

[Joe Van Dyk]

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/331AAA90" in the logs and replication has paused. I have other
> > streaming replication slaves that are fine.
>
> Is it a LOG or a PANIC message? Because it's not unexpected to see such
> messages when reaching the end of the local and/or restore_command
> provided WAL.
>

It's a log message. The server is still running, just replication has
paused.


>
> > 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?
>
> We have really no information to answer that question accurately.
>
> So you really need to provide logs and such.
>

I'll try to find something next time it happens.

Joe


>
> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves

[Joe Van Dyk]

On Mon, Oct 27, 2014 at 8:16 AM, basti  wrote:

> Hello,
>
> months ago I have a similar problem with the OOM-Killer.
> Have a look at
>
> http://www.credativ.co.uk/credativ-blog/2010/03/postgresql-and-linux-memory-management
>
>
Thanks -- my question is not so much about 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 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves

[Joe Van Dyk]

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

[Joe Van Dyk]

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

[Joe Van Dyk]

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', '2014-1-6'));
> >>
> >> -- want back:
> >> --   tstzrange('2014-1-1', '2014-1-4')
> >> --   tstzrange('2014-1-6', '2014-1-6')
> >>
>
> I presume the second output row should be [5,6)...
>

Yes, sorry. And I suppose the third argument to each of those should be
'[)'.


>
> And why are you using a timestamp range when your data are dates?
>

Didn't want to type our the hours, my my real situation involves
timestamptz's.


>
> My first thought is to explode the ranges into distinct dates, order them
> inside a window, use lag(...) to find breaks,p and assign groups, the for
> each group take the min and max of the group and form a new range.  Not
> sure
> exactly what the SQL looks like - especially the range explosion - but
> should technically work even though performance may suck. Probably want to
> use lateral and generate_series(...) if you are on a more recent version.
>

Thanks! I'll look into this.


>
> David J.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Finding-date-intersections-tp5824102p5824194.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.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] Finding date intersections

[Joe Van Dyk]

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')),
  (tstzrange('2014-1-2', '2014-1-4')),
  (tstzrange('2014-1-5', '2014-1-6'));

-- want back:
--   tstzrange('2014-1-1', '2014-1-4')
--   tstzrange('2014-1-6', '2014-1-6')

Thanks,
Joe

[GENERAL] some queries on standby preventing replication updates

[Joe Van Dyk]

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 or so), replication updates are paused.
Is there a way to fix this?

Thanks,
Joe

Re: [GENERAL] jsonb and comparison operators

[Joe Van Dyk]

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 expression B-Tree index on 'a' it would work for
> 'a', but you'd have to use a jsonb literal, not a json/int4 literal.
> If you want to be able to query every key at the top nesting level of
> an object, such that all rows are returned with jsonbs that have
> object values of which in each case one of them is, say, below 10,
>

Just a particular key specified in the query, not just any of them.

I may want key 'a' one time, and 'b' the next time. Not sure if that's what
you meant.

I figured since I could do equality, I should be able to do less than and
greater than.

Joe



> then that's something that no existing opclass can support. But, why
> should it be supported? That's a very fuzzy criteria to search on.
>
> --
> Regards,
> Peter Geoghegan
>

[GENERAL] jsonb and comparison operators

[Joe Van Dyk]

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)::jsonb from
generate_series(1, 10) i;
create index on test using gin(j);

vacuum analyze test;



select * from test where (j->>'a')::int < 10;

I tried
select * from test where j->'a' < 10::json::jsonb;
but didn't seem to use the index.

Re: [GENERAL] adding a nullable column of type domain w/ check constraint runs checks?

[Joe Van Dyk]

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
>>> 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 there a way around this?
>>>
>>> BEGIN
>>>   Timing is on.
>>>
>>> create domain test_enum numeric check (value > 0);
>>>   CREATE DOMAIN
>>>   Time: 1.817 ms
>>>
>>> create table test_enum_table (id serial primary key);
>>>   CREATE TABLE
>>>   Time: 2.213 ms
>>>
>>> insert into test_enum_table select * from generate_series(1, 200);
>>>   INSERT 0 200
>>>   Time: 4299.000 ms
>>>
>>> alter table test_enum_table add column t test_enum;
>>>   ALTER TABLE
>>>   Time: 3165.869 ms -- Takes 3 seconds in this test example
>>>
>>> Also:
>>
>> alter table test_enum_table add column t1 numeric check (t1 > 0);
>>   ALTER TABLE
>>   Time: 140.185 ms
>>
>> which is much more reasonable.
>>
>
> johnto on irc says:
>
> "I'm not sure why it's done this way. it seems like it could test the
> domain once against NULL and see whether that's rejected or not.  instead
> it just forces a rewrite :-("
>

Would it be possible to check the domain against null and if that works,
then don't check any of the rows?

Joe

Re: [GENERAL] adding a nullable column of type domain w/ check constraint runs checks?

[Joe Van Dyk]

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
>> 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 there a way around this?
>>
>> BEGIN
>>   Timing is on.
>>
>> create domain test_enum numeric check (value > 0);
>>   CREATE DOMAIN
>>   Time: 1.817 ms
>>
>> create table test_enum_table (id serial primary key);
>>   CREATE TABLE
>>   Time: 2.213 ms
>>
>> insert into test_enum_table select * from generate_series(1, 200);
>>   INSERT 0 200
>>   Time: 4299.000 ms
>>
>> alter table test_enum_table add column t test_enum;
>>   ALTER TABLE
>>   Time: 3165.869 ms -- Takes 3 seconds in this test example
>>
>> Also:
>
> alter table test_enum_table add column t1 numeric check (t1 > 0);
>   ALTER TABLE
>   Time: 140.185 ms
>
> which is much more reasonable.
>

johnto on irc says:

"I'm not sure why it's done this way. it seems like it could test the
domain once against NULL and see whether that's rejected or not.  instead
it just forces a rewrite :-("

Re: [GENERAL] adding a nullable column of type domain w/ check constraint runs checks?

[Joe Van Dyk]

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 constraint is being checked for each row, even
> though the column can be nullable? Is there a way around this?
>
> BEGIN
>   Timing is on.
>
> create domain test_enum numeric check (value > 0);
>   CREATE DOMAIN
>   Time: 1.817 ms
>
> create table test_enum_table (id serial primary key);
>   CREATE TABLE
>   Time: 2.213 ms
>
> insert into test_enum_table select * from generate_series(1, 200);
>   INSERT 0 200
>   Time: 4299.000 ms
>
> alter table test_enum_table add column t test_enum;
>   ALTER TABLE
>   Time: 3165.869 ms -- Takes 3 seconds in this test example
>
> Also:

alter table test_enum_table add column t1 numeric check (t1 > 0);
  ALTER TABLE
  Time: 140.185 ms

which is much more reasonable.

[GENERAL] adding a nullable column of type domain w/ check constraint runs checks?

[Joe Van Dyk]

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 there a way around this?

BEGIN
  Timing is on.

create domain test_enum numeric check (value > 0);
  CREATE DOMAIN
  Time: 1.817 ms

create table test_enum_table (id serial primary key);
  CREATE TABLE
  Time: 2.213 ms

insert into test_enum_table select * from generate_series(1, 200);
  INSERT 0 200
  Time: 4299.000 ms

alter table test_enum_table add column t test_enum;
  ALTER TABLE
  Time: 3165.869 ms -- Takes 3 seconds in this test example

Re: [GENERAL] Do foreign key triggers get ran even if the key's value doesn't change?

[Joe Van Dyk]

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, which would mean that the number
>>> of referencing tables wouldn't matter.
>
>> But that condition is checked for each constraint individually, not for all
>> constraints simultaneously.  A table can be referenced on multiple
>> combinations of columns, so just one check may not suffice.  I guess the
>> triggers could be organized into groups of identical firing criteria and
>> then checked only once per group, but that seems like a pretty obscure
>> optimization to make.  I don't know how you would reorganize such groupings
>> in a concurrency safe way when constraints were added or removed.
>
> FWIW, I profiled this example (after cranking it up to 500 target tables
> just because).  AFAICT the primary component of the runtime increase is
> query startup overhead associated with the increased number of target
> tables.

I must be missing something, there's only one table being updated?

  start_time = clock_timestamp();

  FOR i IN 1..10 LOOP
UPDATE test_fk SET junk = ''
 WHERE id = i;
  END LOOP;

  end_time = clock_timestamp();

Joe


> If the UPDATE were touching more than one tuple then it might
> get to the point where per-tuple costs dominate, but it's not there in
> this example.  If we tried to do something like what Jeff suggests to
> improve the per-tuple costs, it could actually make this example slower
> by adding more startup overhead.
>
> 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] Do foreign key triggers get ran even if the key's value doesn't change?

[Joe Van Dyk]

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 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 the
>> value doesn't change.
>>
>
> That's kind of a question of definitions.  Perhaps the trigger itself
> doesn't need to run, but the code that decides whether the trigger needs to
> run does need to run.  Where do you draw the line around what is the
> trigger proper and what is just infrastructure?
>
> However you wish to define it, change your function so that it actually
> does change the key field, and see how much slower that is than the
> behavior where you update the row without updating the key.
>
>

I was expecting that the RI update triggers would have a "when (new.key is
distinct from old.key)" condition on them, which would mean that the number
of referencing tables wouldn't matter.



> Cheers,
>
> Jeff
>

[GENERAL] Do foreign key triggers get ran even if the key's value doesn't change?

[Joe Van Dyk]

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 the
value doesn't change.

begin;

set client_min_messages='warning';

CREATE OR REPLACE FUNCTION fnc_check_fk_overhead(key_count INT)
RETURNS INTERVAL AS
$$
DECLARE
  i INT;
  start_time TIMESTAMP;
  end_time TIMESTAMP;
BEGIN
  DROP TABLE if exists test_fk CASCADE;

  CREATE TABLE test_fk
  (
id   BIGINT PRIMARY KEY,
junk VARCHAR
  );

  INSERT INTO test_fk
  SELECT generate_series(1, 10), repeat(' ', 20);

  CLUSTER test_fk_pkey ON test_fk;

  FOR i IN 1..key_count LOOP
EXECUTE 'CREATE TABLE test_fk_ref_' || i ||
' (test_fk_id BIGINT REFERENCES test_fk (id) ON UPDATE NO ACTION)';
  END LOOP;

  start_time = clock_timestamp();

  FOR i IN 1..10 LOOP
UPDATE test_fk SET junk = ''
 WHERE id = i;
  END LOOP;

  end_time = clock_timestamp();

  FOR i IN 1..key_count LOOP
EXECUTE 'DROP TABLE test_fk_ref_' || i;
  END LOOP;

  RETURN end_time - start_time;

END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT fnc_check_fk_overhead(1);
SELECT fnc_check_fk_overhead(50);


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Adding a not null constraint faster

[Joe Van Dyk]

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 null constraint, pg could lock the table, use
that index to see if there's any nulls, and if there's not, add the
constraint.

(That's not possible already, right? I couldn't figure it out if it was.)

Right now, the strategy for adding a not null constraint to a column
without any significant downtime involves creating a new table, as I
understand it.

Joe


-- 
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] SSD Drives

[Joe Van Dyk]

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/announcing-the-next-generation-of-amazon-ec2-high-i/o-instance

Re: [GENERAL] can't cast hstore to json

[Joe Van Dyk]

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
> LINE 1: select hstore('a', 'b')::json;
>^
> # select array_to_json(array[hstore('a', 'b')]);
>   array_to_json
> --
>  ["\"a\"=>\"b\""]
> (1 row)
>
> (I expected  [{"a": "b"}] )
>
> I don't get this on other postgresql installations. Any ideas?
>
> Joe
>

ALTER EXTENSION hstore UPDATE; fixed the problem. (thanks oicu!)

[GENERAL] can't cast hstore to json

[Joe Van Dyk]

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
--
 ["\"a\"=>\"b\""]
(1 row)

(I expected  [{"a": "b"}] )

I don't get this on other postgresql installations. Any ideas?

Joe

[GENERAL] roles inheriting configuration values

[Joe Van Dyk]

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 f1 login;
CREATE ROLE
monkey=# alter role f1 set join_collapse_limit=20;
ALTER ROLE


$ psql --user f1 monkey
psql (9.3.1)
Type "help" for help.

monkey=> show join_collapse_limit ;
 join_collapse_limit
-
 20
(1 row)


$ psql --user f2 monkey
psql (9.3.1)
Type "help" for help.

monkey=> show join_collapse_limit ;
 join_collapse_limit
-
 8
(1 row)

Re: [GENERAL] Is there a way to return "true"/"false" string for boolean type?

[Joe Van Dyk]

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 CASE WHEN ... clause?
> I mean global postgres configuration setting to return 'true'/'false'
> instead of t/f.
>

Question for others..

hstore(record) doesn't seem to cast the rows to text using the normal text
cast, right? Why is that?

If I have a table with a boolean column and I call hstore(table), I see
't', not 'true'.

But true::text is 'true' and false::text is 'false'.

Re: [GENERAL] When starting postgres, it hangs like it is still connected to stdout

[Joe Van Dyk]

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 ends running.
>
> I can't seem to figure out why.
>
> This is postgres 9.2.
>
> What am I doing wrong?
>
> Thanks,
> Susan
>

$ postgres -D $PGDATA &

should start postgres in the background. (the '&' at the end tells it to
put the process in the backgroudn)

If you want it running outside your shell, you should use pg_ctl.
 $ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL
server.

Re: [GENERAL] Is there a way to return "true"/"false" string for boolean type?

[Joe Van Dyk]

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 't' or 'f' for
>>> boolean types.
>>>
>>> Is there a way to return 'true' or 'false' string for boolean type
>>> except using CASE WHEN ... clause?
>>> I mean global postgres configuration setting to return 'true'/'false'
>>> instead of t/f.
>>>
>>> Thank you,
>>> Choon Park
>>>
>>
>> Hi,
>> if you cast the boolean values to text, then you should get
>> 'true'/'false':
>>
>> SELECT true::boolean::text, false::boolean::text;
>>  text | text
>> --+---
>>  true | false
>> (1 row)
>>
>> Does it solve your problem? Why do you want to have true/false instead of
>> t/f?
>>
>> regards,
>> Szymon
>>
>
> In the custom stored function, I'm returning a resultset using hstore
> function.
> RETURN QUERY SELECT a few other columns, hstore(t.*) FROM table t WHERE
> condition.
>
> I don't want to change it to
>
> SELECT a few other columns, hstore('c1', CAST(t.c1 AS TEXT)) ||
> hstore('c2', CAST(t.c2 AS TEXT)) || ...hstore('cn', t.cn::text) || ...
> FROM table t WHERE condition.
>

Can you use json instead of hstore?

# select * from test;
 id | b
+---
  1 | t
  2 | f

# select to_json(test) from test;
  to_json

 {"id":1,"b":true}
 {"id":2,"b":false}

Joe

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

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, 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, 2013 at 3:39 PM, Sergey Konoplev <
> gray...@gmail.com>
> >> >> > wrote:
> >> >> > If I run "COPY (select * from complicate_view) to stdout" on the
> >> >> > standby,
> >> >> > I've noticed that sometimes halts replication updates to the slave.
> >> >>
> >> >> \x
> >> >> select * from pg_stat_repication;
> >>
> >> And it would be very useful to take a look at your checkpoints and
> >> replication configuration parameters on both master and replica.
> >
> > master and replica have same settings.
> >
> > checkpoint_completion_target: 0.9
> > checkpoint_segments: 16
> > checkpoint_timeout: 5m
> > checkpoint_warning: 30s
> > hot_standby: on
> > hot_standby_feedback: on
>
> I meant all the replication settings, see [1]. And pg_stat_statements
> when there is a problem, preferable the error, because when everything
> is okay it is not very useful actually.
>

I don't understand, how is pg_stat_statements helpful here, and what error?

>
> [1]
> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html


max_wal_senders: 5
wal_keep_segments: 1
wal_sender_timeout: 1m
synchronous_standby_names: n/a
vacuum_defer_cleanup_age: 0
max_standby_archive_delay: 30s
max_standby_streaming_delay: -1
wal_receiver_status_interval: 10s
hot_standby_feedback: on
wal_receiver_timeout: 1m

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

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, 2013 at 3:39 PM, Sergey Konoplev 
> >> > wrote:
> >> > If I run "COPY (select * from complicate_view) to stdout" on the
> >> > standby,
> >> > I've noticed that sometimes halts replication updates to the slave.
> >> >
> >> > For example, that's happening right now and "now() -
> >> > pg_last_xact_replay_timestamp()" is 22 minutes. There's many
> >> > transactions
> >> > per second being committed on the master. Once that query is canceled,
> >> > the
> >> > slave catches up immediately.
> >>
> >> And what
> >>
> >> \x
> >> select * from pg_stat_repication;
> >>
> >> shows?
> >
> > on the master, right?
>
> Yes.
>
> And it would be very useful to take a look at your checkpoints and
> replication configuration parameters on both master and replica.
>

master and replica have same settings.

checkpoint_completion_target: 0.9
checkpoint_segments: 16
checkpoint_timeout: 5m
checkpoint_warning: 30s
hot_standby: on
hot_standby_feedback: on

pid  | 10736
usesysid | 10
usename  | postgres
application_name | walreceiver
client_addr  | 
client_hostname  |
client_port  | 47124
backend_start| 2013-12-30 12:08:42.967868-08
state| streaming
sent_location| 410/BC152000
write_location   | 410/BC152000
flush_location   | 410/BC152000
replay_location  | 410/A758B7D0
sync_priority| 0
sync_state   | async

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

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 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.
>
> What do you mean by "COPY on the standby halted replication"?
>

If I run "COPY (select * from complicate_view) to stdout" on the standby,
I've noticed that sometimes halts replication updates to the slave.

For example, that's happening right now and "now() -
pg_last_xact_replay_timestamp()" is 22 minutes. There's many transactions
per second being committed on the master. Once that query is canceled, the
slave catches up immediately.

Joe

Re: [GENERAL] to_json(now()) result doesn't have 'T' separator

[Joe Van Dyk]

I tried making some cast functions as follows, but it doesn't seem to work
properly:

create or replace function iso_timestamp(timestamptz) returns json as $$
  select ('"' ||
  substring(xmlelement(name x, $1)::text from 4 for 32) || '"'
)::json
$$ language sql immutable;

create cast (timestamptz as json) with function iso_timestamp (timestamptz)
as implicit;

create function to_json(timestamptz) returns json as $$
  select $1::json
$$ language sql immutable;

create table t (id serial primary key, created_at timestamptz default
now());
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  wrote:

> This has been brought up a few times in the past:
>
> http://www.postgresql.org/message-id/CAAZKuFZF5=raA=rlncqeg_8gsj9vi4_e-fi1aomk4zp+dxc...@mail.gmail.com
>
> http://www.postgresql.org/message-id/ec26f5ce-9f3b-40c9-bf23-f0c2b96e3...@gmail.com
>
> Any chance it could be fixed? I can't figure out a way 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 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" so it's
>>>> interchangeable with more systems.
>>>>
>>>
>>> Actually, "2013-12-20T15:53:39.44553-08:00" would be ideal.
>>>
>>
>> I dug into the docs some more, and I found this at
>> http://www.postgresql.org/docs/9.3/static/datatype-datetime.html:
>> "Note: ISO 8601 specifies the use of uppercase letter T to separate the
>> date and time.PostgreSQL accepts that format on input, but on output it
>> uses a space rather than T, as shown above. This is for readability and for
>> consistency with RFC 3339 as well as some other database systems."
>>
>> So I looked up RFC 3339 (http://www.ietf.org/rfc/rfc3339.txt) and read:
>>  "NOTE: ISO 8601 defines date and time separated by "T".  Applications
>> using this syntax may choose, for the sake of readability, to specify a
>> full-date and full-time separated by (say) a space character."
>>
>> Doesn't seem like including the 'T' separator would be inconsistent with
>> RFC 3399?
>>
>> I'm sending the output of to_json(now()) to web browsers. Most browsers
>> aren't able to parse the date strings if they are missing the 'T'
>> separator. If datetime strings could include the 'T' time separator and the
>> full timezone, that would make generating json that worked with web
>> browsers much simpler.
>>
>> Joe
>>
>>
>>
>>>
>>>>
>>>>
>>>> http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representationssays
>>>>  'T' can be omitted "by mutual agreement".
>>>>
>>>> I'm working with javascript/json systems that expect the 'T' to be
>>>> there however, so there's no mutual agreement happening.
>>>>
>>>> Thoughts? I know I can hack around it by specifying my own date format,
>>>> but I'd really like to be able to use row_to_json and other functions
>>>> without specifying custom date formats everywhere.
>>>>
>>>> Joe
>>>>
>>>
>>>
>>
>

Re: [GENERAL] to_json(now()) result doesn't have 'T' separator

[Joe Van Dyk]

This has been brought up a few times in the past:
http://www.postgresql.org/message-id/CAAZKuFZF5=raA=rlncqeg_8gsj9vi4_e-fi1aomk4zp+dxc...@mail.gmail.com
http://www.postgresql.org/message-id/ec26f5ce-9f3b-40c9-bf23-f0c2b96e3...@gmail.com

Any chance it could be fixed? I can't figure out a way 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 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" so it's
>>> interchangeable with more systems.
>>>
>>
>> Actually, "2013-12-20T15:53:39.44553-08:00" would be ideal.
>>
>
> I dug into the docs some more, and I found this at
> http://www.postgresql.org/docs/9.3/static/datatype-datetime.html:
> "Note: ISO 8601 specifies the use of uppercase letter T to separate the
> date and time.PostgreSQL accepts that format on input, but on output it
> uses a space rather than T, as shown above. This is for readability and for
> consistency with RFC 3339 as well as some other database systems."
>
> So I looked up RFC 3339 (http://www.ietf.org/rfc/rfc3339.txt) and read:
>  "NOTE: ISO 8601 defines date and time separated by "T".  Applications
> using this syntax may choose, for the sake of readability, to specify a
> full-date and full-time separated by (say) a space character."
>
> Doesn't seem like including the 'T' separator would be inconsistent with
> RFC 3399?
>
> I'm sending the output of to_json(now()) to web browsers. Most browsers
> aren't able to parse the date strings if they are missing the 'T'
> separator. If datetime strings could include the 'T' time separator and the
> full timezone, that would make generating json that worked with web
> browsers much simpler.
>
> Joe
>
>
>
>>
>>>
>>>
>>> http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representationssays
>>>  'T' can be omitted "by mutual agreement".
>>>
>>> I'm working with javascript/json systems that expect the 'T' to be there
>>> however, so there's no mutual agreement happening.
>>>
>>> Thoughts? I know I can hack around it by specifying my own date format,
>>> but I'd really like to be able to use row_to_json and other functions
>>> without specifying custom date formats everywhere.
>>>
>>> Joe
>>>
>>
>>
>

Re: [GENERAL] to_json(now()) result doesn't have 'T' separator

[Joe Van Dyk]

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 row)
>>
>> I'd like to see it output "2013-12-20T15:53:39.098204-08" so it's
>> interchangeable with more systems.
>>
>
> Actually, "2013-12-20T15:53:39.44553-08:00" would be ideal.
>

I dug into the docs some more, and I found this at
http://www.postgresql.org/docs/9.3/static/datatype-datetime.html:
"Note: ISO 8601 specifies the use of uppercase letter T to separate the
date and time.PostgreSQL accepts that format on input, but on output it
uses a space rather than T, as shown above. This is for readability and for
consistency with RFC 3339 as well as some other database systems."

So I looked up RFC 3339 (http://www.ietf.org/rfc/rfc3339.txt) and read:
 "NOTE: ISO 8601 defines date and time separated by "T".  Applications
using this syntax may choose, for the sake of readability, to specify a
full-date and full-time separated by (say) a space character."

Doesn't seem like including the 'T' separator would be inconsistent with
RFC 3399?

I'm sending the output of to_json(now()) to web browsers. Most browsers
aren't able to parse the date strings if they are missing the 'T'
separator. If datetime strings could include the 'T' time separator and the
full timezone, that would make generating json that worked with web
browsers much simpler.

Joe



>
>>
>>
>> http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representationssays
>>  'T' can be omitted "by mutual agreement".
>>
>> I'm working with javascript/json systems that expect the 'T' to be there
>> however, so there's no mutual agreement happening.
>>
>> Thoughts? I know I can hack around it by specifying my own date format,
>> but I'd really like to be able to use row_to_json and other functions
>> without specifying custom date formats everywhere.
>>
>> Joe
>>
>
>

Re: [GENERAL] to_json(now()) result doesn't have 'T' separator

[Joe Van Dyk]

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" so it's
> interchangeable with more systems.
>

Actually, "2013-12-20T15:53:39.44553-08:00" would be ideal.


>
>
> http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representationssays
>  'T' can be omitted "by mutual agreement".
>
> I'm working with javascript/json systems that expect the 'T' to be there
> however, so there's no mutual agreement happening.
>
> Thoughts? I know I can hack around it by specifying my own date format,
> but I'd really like to be able to use row_to_json and other functions
> without specifying custom date formats everywhere.
>
> Joe
>

[GENERAL] to_json(now()) result doesn't have 'T' separator

[Joe Van Dyk]

# 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_representationssays
'T' can be omitted "by mutual agreement".

I'm working with javascript/json systems that expect the 'T' to be there
however, so there's no mutual agreement happening.

Thoughts? I know I can hack around it by specifying my own date format, but
I'd really like to be able to use row_to_json and other functions without
specifying custom date formats everywhere.

Joe

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

A possibly related question:

I've set wal_keep_segments to 10,000 and also have archive_command running
wal-e. I'm seeing my wal files disappear from pg_xlog after 30 minutes. Is
that expected? Is there a way around that?

(I want to use streaming replication and wal-e for PITR restores)


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-running query at 9:30 am and replication data started catching up.
>
> The data up until 10 am got restored fine (took until 10:30 am to restore
> that much). Then I started getting errors like "FATAL:  could not receive
> data from WAL stream: ERROR:  requested WAL segment
> 000103C30086 has already been removed".
>
> I'm confused about how pg could restore data from 3:30 am to 10 am, then
> start complaining about missing WAL files.
>
> What's the best way to avoid this problem? Increase wal_keep_segments?
>
> Joe
>

[GENERAL] Replication failed after stalling

[Joe Van Dyk]

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 10 am got restored fine (took until 10:30 am to restore
that much). Then I started getting errors like "FATAL:  could not receive
data from WAL stream: ERROR:  requested WAL segment
000103C30086 has already been removed".

I'm confused about how pg could restore data from 3:30 am to 10 am, then
start complaining about missing WAL files.

What's the best way to avoid this problem? Increase wal_keep_segments?

Joe

Re: [GENERAL] Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?

[Joe Van Dyk]

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 function is STABLE and is usually inlined and takes 2 ms to run.
>
> > Immediately, the function quit being inlined and took 1500ms to run.
>
> > Changing the function back to SECURITY DEFINER let the function be
> inlined
> > again.
>
> > On postgresql 9.3.1.
>
> > Is this expected behavior?
>
> Yes.  SECURITY DEFINER functions can't be inlined --- there would be
> noplace to effect the change of user ID.
>
> regards, tom lane
>

Thanks. Is that documented somewhere? I looked, couldn't find anything.

[GENERAL] Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?

[Joe Van Dyk]

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.

Changing the function back to SECURITY DEFINER let the function be inlined
again.

On postgresql 9.3.1.

Is this expected behavior?

[GENERAL] Report the trigger name when complaining about "tuple to be updated was already modified by an operation triggered by the current command"?

[Joe Van Dyk]

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 "channels_products" WHERE
"channels_products"."product_id" = 53 AND "channels_products"."channel_id"
IN (18, 24, 26, 53, 57, 76, 88)


This was the complete entry in the log. There was no mention about what
trigger or function caused the problem. Can there be?

(I fixed the error by moving the before trigger to an after one.)

Joe

Re: [GENERAL] uuids with btree_gist

[Joe Van Dyk]

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, lifetime tstzrange, value text,
> test(# exclude using gist (item with =, lifetime with &&));
> ERROR:  data type uuid has no default operator class for access method
> "gist"
> HINT:  You must specify an operator class for the index or define a
> default operator class for the data type.
> test=#
>
> It works fine if I make item a text field.
>
> test=# create table v(item text, lifetime tstzrange, value text,
> test(# exclude using gist (item with =, lifetime with &&));
> CREATE TABLE
> test=#
>
> Is there any reason that the required uuid access method isn't
> implemented?  How hard is it to implement this?
>

I would also like to see btree_gist support uuid's.

I found a related thread from two years ago:
http://postgresql.1045698.n5.nabble.com/UUID-datatype-GiST-index-support-td4722960.html

[GENERAL] Why doesn't COPY support the HEADER options for tab-separated output?

[Joe Van Dyk]

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

[GENERAL] Adding ip4r to Postgresql core?

[Joe Van Dyk]

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

Re: [GENERAL] casting tsrange to tstzrange doesn't seem to work?

[Joe Van Dyk]

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 be a cast between tsrange and tstzrange.
>
> Unfortunately, this cant work generally for all range types, because the
> total order might be different. For instance, we can't cast between a
> textrange and int4range, because:
>
>['09','1']
>
> is a valid text range, but:
>
>[9,1]
>
> is not.
>
> Regards,
> Jeff Davis
>

Worth it to file a bug for this?

Re: [GENERAL] how _not_ to log?

[Joe Van Dyk]

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:  create
> role pguser encrypted password 'XXX';
>
> Where XXX is the actual password.  This happens every 30 minutes
> when my chef client kicks off and resets the passwords.  Here's everything
> that I have in postgres.conf related to logging:
>
> log_destination = 'syslog'  # Valid values are combinations of
> # stderr, csvlog, syslog, and
> eventlog,
> # depending on platform.  csvlog
> # requires logging_collector to be
> on.
> logging_collector = on  # Enable capturing of stderr and
> csvlog
> # into log files. Required to be
> on for
> # csvlogs.
> log_directory = 'pg_log'# directory where log files are
> written,
> log_filename = 'postgresql-%a.log'  # log file name pattern,
> log_truncate_on_rotation = on   # If on, an existing log file with
> the
> # same name as the new log file
> will be
> log_rotation_age = 1d   # Automatic rotation of logfiles
> will
> log_rotation_size = 0   # Automatic rotation of logfiles
> will
> # happen after that much log
> output.
> # DO NOT USE without syslog or
> # logging_collector
> log_min_duration_statement = 2000   # 2 seconds
> log_checkpoints = on
>
> What I'd like to do is stop logging create role commands, as the
> logs end up full of passwords.  Is there any way to do this?  Thanks, and
> have fun!


Have chef supply the password in encrypted format.

It's not that well documented yet though, as far as I can tell.  See this
thread:
http://www.postgresql.org/message-id/201110272054.p9rksks18...@momjian.us

Seems like that information should be in the CREATE ROLE docs.




>
> -tspencer
>
>
>
> --
> 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] cron tasks in pg 9.3

[Joe Van Dyk]

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,
>> 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 run whatever functions are necessary.
>>
>> It would be super to let postgresql handle all of that.
>>
>
> What about pgAgent?
>
> http://www.pgadmin.org/docs/1.**16/pgagent.html<http://www.pgadmin.org/docs/1.16/pgagent.html>


That falls into the category of having to run another singleton (exactly
one instance running) bit of software at all times. Not much difference
between pgagent and cron, as far as I can tell.

I already always have a single postgresql master running at all times, it
would be awesome the postgresql master process could be used to fire off
these scheduled tasks. It seems like that would simplify many systems.

Joe

[GENERAL] cron tasks in pg 9.3

[Joe Van Dyk]

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 run
whatever functions are necessary.

It would be super to let postgresql handle all of that.

Joe

Re: [GENERAL] plpgsql plan caching allowing invalid data to enter table?

[Joe Van Dyk]

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:
>>
>> 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_var my_domain := $1;
>> begin
>> insert into my_table values (my_var);
>> end $$ language plpgsql;
>>
>> Session 2:
>> select f('test');
>> delete from my_table;
>> -- Keep session open!
>>
>> Session 1:
>> alter domain my_domain drop constraint my_domain_check;
>> alter domain my_domain add constraint my_domain_check check
>> (length(value) > 5);
>>
>> Session 2:
>> select f('test');
>> -- This works, but it should fail.
>> -- I have a constraint of more than 5 characters on the domain.
>> -- But I can insert a row with 4 characters.
>>
>
> My guess this has more to do with MVCC. Session 1 and 2 are looking at
> different snapshots of the database and acting accordingly.


Hm, I'd be surprised -- there's no multi-statement transactions used here.
 My guess is that the check constraint gets cached by the plpgsql function
and there's no check of the constraint when the data is being inserted
inside the function body.

In any event, I shouldn't be allowed to have data in a table that violates
a check constraint.

Re: [GENERAL] plpgsql plan caching allowing invalid data to enter table?

[Joe Van Dyk]

As you can see, I have data in my_table that violates the check constraint.


# select * from my_table;
 name
──
 test
(1 row)


# \d+ my_table
Table "public.my_table"
 Column │   Type│ Modifiers │ Storage  │ Stats target │ Description
┼───┼───┼──┼──┼─
 name   │ my_domain │   │ extended │  │
Has OIDs: no


# \dD my_domain
 List of domains
 Schema │   Name│ Type │ Modifier │   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?
>
> 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_var my_domain := $1;
> begin
>insert into my_table values (my_var);
> end $$ language plpgsql;
>
> Session 2:
> select f('test');
> delete from my_table;
> -- Keep session open!
>
> Session 1:
> alter domain my_domain drop constraint my_domain_check;
> alter domain my_domain add constraint my_domain_check check (length(value)
> > 5);
>
> Session 2:
> select f('test');
> -- This works, but it should fail.
> -- I have a constraint of more than 5 characters on the domain.
> -- But I can insert a row with 4 characters.
>

[GENERAL] plpgsql plan caching allowing invalid data to enter table?

[Joe Van Dyk]

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_var my_domain := $1;
begin
   insert into my_table values (my_var);
end $$ language plpgsql;

Session 2:
select f('test');
delete from my_table;
-- Keep session open!

Session 1:
alter domain my_domain drop constraint my_domain_check;
alter domain my_domain add constraint my_domain_check check (length(value)
> 5);

Session 2:
select f('test');
-- This works, but it should fail.
-- I have a constraint of more than 5 characters on the domain.
-- But I can insert a row with 4 characters.

[GENERAL] domains, case statements, functions: bug?

[Joe Van Dyk]

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"

Re: [GENERAL] odd locking behaviour

[Joe Van Dyk]

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 is a basic question and should be widely
>> understood but
>> I don't see it described in the documentation as far as I could find.
>>
>> I'm using Postgres 8.4.13
>>
>> I have two tables, call them A & B for example purposes.
>>
>> Table A, with column id
>>
>> Table B
>>   - foreign key reference a_id matches A.id FULL
>>   - some other columns blah1, blah2, blah3
>>
>> I do this:
>>
>> db1: begin
>>   db2: begin
>> db1: select A FOR UPDATE
>>   db2: update B set blah1 = 42; --- OK, UPDATE 1
>>   db2: update B set blah2 = 42; --- This blocks waiting for a lock on A!!
>>
>> Here are the exact steps to reproduce:
>>
>> CREATE TABLE A (id bigint NOT NULL);
>> CREATE TABLE B (id bigint NOT NULL, a_id bigint NOT NULL, blah1 bigint,
>> blah2 bigint, blah3 bigint);
>> ALTER TABLE ONLY A ADD CONSTRAINT a__pkey PRIMARY KEY (id);
>> ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id)
>> MATCH FULL;
>> INSERT INTO A VALUES (1);
>> INSERT INTO B VALUES (1, 1, 1, 2, 3);
>>
>> Now, in two DB connections, CON1 and CON2.
>>
>> CON1:
>>   BEGIN;
>>   SELECT * FROM A WHERE id = 1 FOR UPDATE;
>>
>> CON2:
>>   BEGIN;
>>   UPDATE B SET blah1 = 42 WHERE id = 1;
>>   UPDATE B SET blah2 = 42 WHERE id = 1; -- this blocks
>>
>> I have verified that if I drop the foreign key constraint requiring
>> B.a_id match A.id
>> that this behaviour does not happen and both updates succeed without
>> blocking.
>>
>> I can perhaps understand why it acquires a shared lock on A when updating
>> B because of
>> the foreign key reference, even though it doesn't seem like it should
>> require it because
>> the columns being updated are not relevant to the foreign key constraint.
>>
>> That behaviour would be less than ideal but at least it would be
>> understandable.
>>
>> However, why does it only try to acquire the lock on the second update
>>
>> If I do a ROLLBACK in CON1, then I see CON2 finish the UPDATE and it
>> acquires a
>> lock on table A.  Why?
>>
>> Thank you.
>>
>>
>
>
> --
> Moshe Jacobson
> Nead Werx, Inc. | Manager of Systems Engineering
> 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> mo...@neadwerx.com | www.neadwerx.com
>
> "Quality is not an act, it is a habit." -- Aristotle
>

Re: [GENERAL] Efficiency of materialized views refresh in 9.3

[Joe Van Dyk]

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_view;
> Nope. Here is some documentation:
> http://www.postgresql.org/docs/devel/static/rules-materializedviews.html
>
>
I meant for how postgres handles the refresh behind the scenes, not how the
user actually does a refresh.

[GENERAL] Efficiency of materialized views refresh in 9.3

[Joe Van Dyk]

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

Re: [GENERAL] Analyzing last run query in psql

[Joe Van Dyk]

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:
> > 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 files into psql (if it matters).
>
> There is no history or something like that, but you can use autoexplain.
> http://www.postgresql.org/docs/9.2/interactive/auto-explain.html
>
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.  (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
>
>
> --
> 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] Analyzing last run query in psql

[Joe Van Dyk]

On Wed, Jul 3, 2013 at 6:43 AM, Oleg Bartunov  wrote:

> It was my dream to have something we already have in shell -
>
> explain analyze !$
>

It would probably be: explain analyze !!

(at least in bash syntax)

Joe


>
> I think it should be not very difficult.
>
> Oleg
>
> 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:
>>explain analyze $LAST
>>
>> (or can I do something like that already?)
>>
>> I'm not using psql interactively, I pipe files into psql (if it matters).
>>
>> Joe
>>
>>
> Regards,
> Oleg
> __**__**_
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>

[GENERAL] Analyzing last run query in psql

[Joe Van Dyk]

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 files into psql (if it matters).

Joe

[GENERAL] Really poor gist index performance with tstzrange types

[Joe Van Dyk]

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;

create table f (duration tstzrange);

insert into f
  select tstzrange(now() - '1 month'::interval, now() - '1
sec'::interval) from generate_series(1, 10);

create index on f using gist(duration);
analyze f;

select count(*) from f where tstzrange(now(), now(), '[]') << duration;
-- returns 0

explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') << duration;

 Aggregate  (cost=2720.36..2720.37 rows=1 width=0) (actual
time=55.374..55.374 rows=1 loops=1)
   ->  Seq Scan on f  (cost=0.00..2637.02 rows=4 width=0) (actual
time=55.369..55.369 rows=0 loops=1)
 Filter: (tstzrange(now(), now(), '[]'::text) << duration)
 Rows Removed by Filter: 10
 Total runtime: 55.407 ms

[GENERAL] casting tsrange to tstzrange doesn't seem to work?

[Joe Van Dyk]

# 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.

Re: [GENERAL] PostgreSQL Backup Booklet

[Joe Van Dyk]

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 who are interested:

http://www.amazon.com/Instant-PostgreSQL-Backup-Restore-How/dp/1782169105

http://www.packtpub.com/how-to-postgresql-backup-and-restore/book

Re: [GENERAL] Using varchar primary keys.

[Joe Van Dyk]

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 
> >> 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 table (i.e. id, name) one might add any
> number
> >>> of columns later, and you'll be glad to have it.
> >>>
> >>
> >> Nothing prevents you from adding more columns if you use varchar primary
> >> keys.
> >>
> >>>
> >>>
> >>> My preferred method is to give every table an ID column of UUID type
> and
> >>> generate a UUID using the uuid-ossp contrib module. This also prevents
> >>> someone not familiar with the database design from using an ID
> somewhere
> >>> they should not (as is possible with natural PKs) or treating the ID
> as an
> >>> integer, not an identifier (as is all too common with serial integers).
> >>>
> >>>
> >>
> >> This would be a concern if you had multi master writes . As far as I
> know
> >> Postgres does not have a true multi master replication system so all the
> >> writes have to happen on one server right?
> >>
> >> As for UUIDs I use them sometimes but I tend to also use one serial
> column
> >> because when I am communicating with people it makes it so much easier
> to
> >> say "dealer number X" than "dealer number SOME_HUGE_UUID".  I often
> have to
> >> talk to people about the data and UUIDs make it very difficult to
> >> communicate with humans.
> >
> >
> > I've been wishing for a smaller uuid type for a while. Say you need to
> > assign a Order #. Customers might be referencing the number, so you don't
> > want it to be too long. But you don't want Order #'s to be guessable or
> have
> > the characters/digits be transposed accidently.
> >
> > I've been using a unique text column with a default of
> random_characters(12)
> >
> > CREATE OR REPLACE FUNCTION public.random_characters(length integer)
> >  RETURNS text
> >  LANGUAGE sql
> >  STABLE
> > AS $function$
> > SELECT array_to_string(array((
> >   SELECT SUBSTRING('abcdefghjkmnpqrstuvwxyz23456789'
> > FROM mod((random()*31)::int, 31)+1 FOR 1)
> >   FROM generate_series(1, $1))),'');
> > $function$;
> >
> > This seems to work ok. I don't allow 1's or l's or 0's or O's, as they
> can
> > easily be mistyped or misread.
>
> This is pseudo random and can be guessed, which is maybe dangerous
> depending on circumstance.  For stronger random stream go to
> pgcrypto.gen_random_bytes().  Also, now you have to worry about
> collisions -- the whole point of uuid is to try and keep you from
> having to deal with that.
>

Right, but it's better than using serial's as far as being guessable.

The probability for collisions are fairly low, if you are using 12 or more
characters (with 30 possible characters). Not sure what the math is on the
probability of collisions (birthday problem) though.. and you could have a
trigger that checked for the existence of a matching key before
inserts/updates.

And using UUIDs would be too long for lots of purposes where people are
working with the numbers, and where there might be external constraints on
how long the IDs can be.

An example use case:
https://www.tanga.com/deals/cd8f90c81a/oral-b-sensitive-clean-6-extra-soft-replacement-brush-heads

where "cd8f90c81a" is the 'uuid' for that product. It's non-guessable
enough, and I don't want to put a full UUID in the URL.




>
> My historical comments in this debate are noted.  To summarize, I
> strongly believe that natural keys are often (but not always) better.
>
>

Re: [GENERAL] Using varchar primary keys.

[Joe Van Dyk]

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 table (i.e. id, name) one might add any number
>> of columns later, and you'll be glad to have it.
>>
>>
> Nothing prevents you from adding more columns if you use varchar primary
> keys.
>
>
>>
>> My preferred method is to give every table an ID column of UUID type and
>> generate a UUID using the uuid-ossp contrib module. This also prevents
>> someone not familiar with the database design from using an ID somewhere
>> they should not (as is possible with natural PKs) or treating the ID as an
>> integer, not an identifier (as is all too common with serial integers).
>>
>>
>>
> This would be a concern if you had multi master writes . As far as I know
> Postgres does not have a true multi master replication system so all the
> writes have to happen on one server right?
>
> As for UUIDs I use them sometimes but I tend to also use one serial column
> because when I am communicating with people it makes it so much easier to
> say "dealer number X" than "dealer number SOME_HUGE_UUID".  I often have to
> talk to people about the data and UUIDs make it very difficult to
> communicate with humans.
>

I've been wishing for a smaller uuid type for a while. Say you need to
assign a Order #. Customers might be referencing the number, so you don't
want it to be too long. But you don't want Order #'s to be guessable or
have the characters/digits be transposed accidently.

I've been using a unique text column with a default of random_characters(12)

CREATE OR REPLACE FUNCTION public.random_characters(length integer)

 RETURNS text

 LANGUAGE sql

 STABLE

AS $function$

SELECT array_to_string(array((

  SELECT SUBSTRING('abcdefghjkmnpqrstuvwxyz23456789'

FROM mod((random()*31)::int, 31)+1 FOR 1)

  FROM generate_series(1, $1))),'');

$function$;

This seems to work ok. I don't allow 1's or l's or 0's or O's, as they can
easily be mistyped or misread.

Re: [GENERAL] Trigger of Transaction

[Joe Van Dyk]

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); //transaction block start
>
> // Make an insert to one table (Vector)
> // After that I insert the childs of the first table with their parent_id
> like the FK.
> con.commit(); //transaction block end
>
> I have this Trigger:
>
> CREATE *TRIGGER *trigger_update_index *AFTER INSERT*
> ON "Vector" FOR EACH ROW
> EXECUTE PROCEDURE update_index();
>
> CREATE OR REPLACE FUNCTION *update_index*() RETURNS *TRIGGER *AS
> $update_index$
> DECLARE
> BEGIN
>  -- Make something
> END;
> $update_index$ LANGUAGE plpgsql;
>
> What's the problem? that when the trigger fire only the Parent (Vector)
> was inserted an not the childs :S so I need that records to be inserted to
> work in my function.
>
> I'm trying to make a Trigger, only to *execute after ALL the transaction*.
> So, after all the INSERTs INTO (like 5 or 10) I want to launch my function.
> I found some information in google about this: "*Constraint Trigger*",
> that perhaps I can tell some rules before triggering but I don't know if it
> is what I need to and also don't know how to code that.
>

create constraint trigger my_trigger_name

after insert on products

deferrable

for each row

execute procedure blah();

"constraint" triggers let you change when the trigger executes.

http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html says "When
the CONSTRAINT option is specified, this command creates a *constraint
trigger*. This is the same as a regular trigger except that the timing of
the trigger firing can be adjusted using SET
CONSTRAINTS.
Constraint triggers must be AFTER ROW triggers. They can be fired either at
the end of the statement causing the triggering event, or at the end of the
containing transaction; in the latter case they are said to be *deferred*.
A pending deferred-trigger firing can also be forced to happen immediately
by using SET CONSTRAINTS. Constraint triggers are expected to raise an
exception when the constraints they implement are violated."



>
> I appreciate your help a lot.
>
> Thanks ;)
>
> JP Cook
>
>

[GENERAL] Group by -- precedence question

[Joe Van Dyk]

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 "group by 1" or use a different name, right?

Re: [GENERAL] Testing Technique when using a DB

[Joe Van Dyk]

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 experiment with pg_restore and template
>> techniques this weekend and see which ones prove viable.
>>
>
> Another possibility a bit outside my area of expertise but what about a VM
> image configured to your needs that you just spin up as needed then discard
> when done (i.e. always spinning up the same starting image)?
>
>
I'd guess the OP is running hundreds of tests, where the data needs to be
reverted/reset after each test, and each individual test might run in, say,
0.1 seconds. This is a really common technique when testing web apps. I
don't think you'd want to start a VM for each of these tests, especially
when the tests are small and specific.

Re: [GENERAL] Testing Technique when using a DB

[Joe Van Dyk]

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 in my case.

The first choice is "truncate" which starts a transaction before each test.


I think you mean "transactional tests" here, not "truncate". While the test
database is truncated once at the start of all the tests, each test runs
inside its own transaction which will never be committed.

 If the testing is within the same process, this works and a roll back
restores the DB.  But if the testing involves two processes, then the test
data entered by the test rig can not be seen by the "system under test"
(SUT).  With Rails, there are times when this is needed.  The test rig
drives a browser which calls into a Rails application.  There are
"dangerous" ways to still use this method but each has various down falls
or risks.


IMO, you don't want to use transactional tests. When you do, ActiveRecord
will use savepoints to mimic transactions. This means now() will never
change during the tests, deferred constraints/triggers won't work, other
processes can't see the data, etc.  The system is behaving differently in
the test environment than in the real one, which is bad.

If you are treating the database as a really dumb store of data, then you
may want to use transactional tests. But be aware of the caveats.


The other two choices are delete and truncate which both end up with an
empty database just after each test.  This prevents any test data that is
already in the database from being used after the first test.  Note that a
"test run" will run through a sequence of tests (usually quite a few).

All of these are fairly fast with each one being "faster" under different
conditions (according to users).

Generally, this pushes the Rails community to have either "fixtures" or
"factories".  Both are ok solutions but both also have problems.  In my
case, I have a dozen or so tables all with very tight constraints and
creating either fixtures or factories is very troublesome.  Also, I have a
real database with real data in production and it seems foolish not to take
advantage of the knowledge contained within that database.  By "knowledge"
I mean the particular values and weirdness within the data that a factory
or a fixture might not realize.

One choice would be to create the database, use it, and then drop it for
each test.  I would create the database from a template that already has
data taken from the production database (and probably trimmed down to a
small subset of it).  This requires some crafty dancing in the Rails set up
since it likes to just attach to a database and run but it could be done.
 From first blush, this sounds like it would be really slow but may be not.

The other choice would be to somehow copy the data to temporary tables
before the test run and then copy it back.  The advantage to this is it is
not very PostgreSQL specific.  Indeed, if the template database is already
set up, then only one copy would be needed at the start of the test.

The other thought I had is if there is some type of "leaky" transaction.  A
transaction where another process can see the data but the roll back would
still work and be effective.  Essentially I'm asking if all the protections
a database offers could be dropped... but I thought I'd see if that was
possible.

The other thought is perhaps there is a "snap shot" type concept.  I don't
see it in the list of SQL commands.  A "snap shot" would do exactly what it
sounds like.  It would take a snap shot and save it somehow.  Then a
"restore to snap shot" would restore the DB back to that state.


This would be super super super awesome, but it doesn't exist as far as I
know. This would be a "permanent snapshot" that could be easily and quickly
restored.

I wonder if it would be possible to make an extension that made this easy
to do.


I thought this group might suggest other ideas and either nuke the really
bad ideas or promote the plausible ideas I've mentioned above.


Make sure to look at database_cleaner if you haven't yet. Also this may be
interesting: https://github.com/bmabey/database_cleaner/issues/80

Personally, I use database_cleaner's delete method, plus I load a SQL file
at the beginning of each test.



Sorry for the long post.  I appreciate your thoughts.

Perry


-- 
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] Joining against a view that uses an aggregate - performance issue

[Joe Van Dyk]

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/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, andres said "Afaics its this
> restriction: "1. The qual must not contain any subselects (mainly because
> I'm not sure it will work correctly: sublinks will already have been
> transformed into subplans in the qual, but not in the subquery)." in
> qual_is_pushdown_safe"
>
> Not sure if there's anything to be done here, just thought I'd post in
> case anyone has any ideas. In an ideal world, I'd be able to write version
> #3.
>
> Joe
>

Re: [GENERAL] Joining against a view that uses an aggregate - performance issue

[Joe Van Dyk]

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/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, andres said "Afaics its this
> restriction: "1. The qual must not contain any subselects (mainly because
> I'm not sure it will work correctly: sublinks will already have been
> transformed into subplans in the qual, but not in the subquery)." in
> qual_is_pushdown_safe"
>
> Not sure if there's anything to be done here, just thought I'd post in
> case anyone has any ideas. In an ideal world, I'd be able to write version
> #3.
>
> Joe
>

[GENERAL] Joining against a view that uses an aggregate - performance issue

[Joe Van Dyk]

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, andres said "Afaics its this
restriction: "1. The qual must not contain any subselects (mainly because
I'm not sure it will work correctly: sublinks will already have been
transformed into subplans in the qual, but not in the subquery)." in
qual_is_pushdown_safe"

Not sure if there's anything to be done here, just thought I'd post in case
anyone has any ideas. In an ideal world, I'd be able to write version #3.

Joe

Re: [GENERAL] subselects vs WITH in views

[Joe Van Dyk]

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 fence", that means
> that means that the planner won't move conditions into or
> out of the WITH query.


Where's the best place to read up on this?

Thanks,
Joe

[GENERAL] subselects vs WITH in views

[Joe Van Dyk]

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 (
  select * from (select code from promotions)_
);

create view promotion_details2 as (
  with info as (select code from promotions) select * from info
);



explain analyze
select * from promotion_details1 where code = 'slickdeals';

explain analyze
select * from promotion_details2 where code = 'slickdeals';


QUERY PLAN
--
 Bitmap Heap Scan on promotions  (cost=72.54..6435.31 rows=3014
width=32) (actual time=0.122..0.196 rows=113 loops=1)
   Recheck Cond: (code = 'slickdeals'::citext)
   ->  Bitmap Index Scan on promotions_code_idx  (cost=0.00..71.79
rows=3014 width=0) (actual time=0.111..0.111 rows=113 loops=1)
 Index Cond: (code = 'slickdeals'::citext)
 Total runtime: 0.236 ms
(5 rows)


 QUERY PLAN
-
 CTE Scan on info  (cost=15539.25..29102.81 rows=3014 width=32)
(actual time=184.303..661.816 rows=113 loops=1)
   Filter: (code = 'slickdeals'::citext)
   Rows Removed by Filter: 602712
   CTE info
 ->  Seq Scan on promotions  (cost=0.00..15539.25 rows=602825
width=32) (actual time=0.018..145.272 rows=602825 loops=1)
 Total runtime: 697.495 ms
(6 rows)

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

[Joe Van Dyk]

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 without the useless "is true" bits.
>
> regards, tom lane
>


Huh, that did do the trick. Why does "is true" affect the plan?

without "is true" in the conditions:
--
 Aggregate  (cost=16676.66..16676.67 rows=1 width=0) (actual time=
95.648..95.648 rows=1 loops=1)
   ->  Bitmap Heap Scan on promotions p  (cost=868.37..16619.49 rows=22868
width=0) (actual time=11.031..95.294 rows=2720 loops=1)
 Recheck Cond: (end_at > (now() - '30 days'::interval))
 Filter: ((quantity = 1) AND (SubPlan 1))
 Rows Removed by Filter: 43073
 ->  Bitmap Index Scan on index_promotions_on_end_at
 (cost=0.00..862.65 rows=46093 width=0) (actual time=10.783..10.783
rows=73234 loops=1)
   Index Cond: (end_at > (now() - '30 days'::interval))
 SubPlan 1
   ->  Index Only Scan using index_promotion_usages_on_promotion_id
on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual
time=0.001..0.001 rows=0 loops=44998)
 Index Cond: (promotion_id = p.id)
 Heap Fetches: 2720
 Total runtime: 95.739 ms
(12 rows)


with "is true" in the conditions:

  QUERY PLAN

--
 Aggregate  (cost=94430.93..94430.94 rows=1 width=0) (actual
time=534.568..534.569 rows=1 loops=1)
   ->  Seq Scan on promotions p  (cost=0.00..94373.76 rows=22868 width=0)
(actual time=0.306..534.165 rows=2720 loops=1)
 Filter: (((quantity = 1) IS TRUE) AND ((end_at > (now() - '30
days'::interval)) IS TRUE) AND ((SubPlan 1) IS TRUE))
 Rows Removed by Filter: 600105
 SubPlan 1
   ->  Index Only Scan using index_promotion_usages_on_promotion_id
on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual
time=0.001..0.001 rows=0 loops=44998)
 Index Cond: (promotion_id = p.id)
 Heap Fetches: 2720
 Total runtime: 534.627 ms
(9 rows)

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

[Joe Van Dyk]

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/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txt>for
>>  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 this case, finding recently-expired
>> promotions, finding promotions that have a quantity of one, and finding
>> promotions that were used.
>>
>> My approach is to put these conditions into views, then I can join
>> against each one. But that approach is much slower than inlining all the
>> code.
>>
>> How is this typically done?
>>
>> Thanks,
>> Joe
>>
>>
>>  From your first example on the gist I extracted this. It should avoid
> the multiple scans and hash join the the join of the two views suffers from.
>
> create view promotions_with_filters as (
>   select *,
> end_at > now() - '30 days'::interval as recently_expired,
> quantity = 1 as one_time_use,
> exists(select 1 from promotion_usages pu on pu.promotion_id = p.id)
> as used
>   from promotions
> );
>
> select count(*) from promotions_with_filters where recently_expired and
> one_time_use;
>


Perhaps I fat-fingered something somewhere... I tried that and I got this:
https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt

The with_filters view uses a different plan.

[GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

[Joe Van Dyk]

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 this case, finding recently-expired
promotions, finding promotions that have a quantity of one, and finding
promotions that were used.

My approach is to put these conditions into views, then I can join against
each one. But that approach is much slower than inlining all the code.

How is this typically done?

Thanks,
Joe

[GENERAL] Quickly making a column non-nullable (without a table scan)

[Joe Van Dyk]

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 via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PITR manual doesn't reference pg_receivexlog?

[Joe Van Dyk]

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?

[GENERAL] alter view foo set () -- fixed in 9.2 stable, but when will it be released?

[Joe Van Dyk]

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

[GENERAL] Shorthand syntax for triggers

[Joe Van Dyk]

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

for each row execute procedure some_trigger();


I wish I could do:

create trigger some_trigger after insert on products
execute procedure do $$ begin
insert into audits values (CHANGED.value);
end $$ language plpgsql;


Changes/improvements:

1. Triggers default to 'for each row'

2. Triggers can use anonymous functions

3. Triggers can access a special CHANGED value that's either NEW for insert
or updates, or OLD for deletes.

4. Default for 'after insert' triggers is to return null, as I believe it
doesn't matter what you return here.

5. Way less repetitive typing.


Thoughts? Is this a terrible idea?

[GENERAL] Fetching json: best way to do this?

[Joe Van Dyk]

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,
 "height":199,
 "id":79
  }
   ],
   "channels":[
  {
 "id":8,
 "name":"Animals",
 "slug":"animals"
  },
  {
 "id":12,
 "name":"Hidden",
 "slug":"hidden"
  }
   ]
}


Is this the best way to get that?

create type image_listing as   (filename text, width int, height int,
id int);
create type channel_listing as (id integer, name text, slug text);
create type product_listing as (
  id integer,
  name text,
  uuid text,
  thumbnails image_listing[],
  channels   channel_listing[]);

create function product_listing_json(product_id integer) returns json
language sql stable as $$
  select row_to_json(
row(
  products.id,
  products.name,
  products.uuid,
  array_agg((m.filename, m.width, m.height, m.id)::image_listing),
  array_agg((c.id, c.title, c.slug)::channel_listing)
 )::product_listing
  )
  from products
  join product_medias m on m.media_of_id = products.id
  left join channels_products cp on cp.product_id = products.id
  join channels c on c.id = cp.channel_id
  where products.id = $1
  group by products.id
$$;


select product_listing_json(id) from products order by id desc;


(https://gist.github.com/377345 contains the above code and expected output)

I'm really looking forward to being able to slurp up a complex json
object in a single sql query, so I'm exploring ways to do that.

Thanks,
Joe


-- 
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] row_to_json question

[Joe Van Dyk]

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"}
> Note that there is no "id" column in the result.
>
>
> create table users (id serial primary key, email_address varchar,
> username varchar);
> insert into users (email_address, username) values ('j...@tanga.com',
> 'joevandyk');

This is the best I can come up with:

select row_to_json(f) from (select email_address, username from users) f;
{"email_address":"j...@tanga.com","username":"joevandyk"}

Is there a cleaner way to do this?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] row_to_json question

[Joe Van Dyk]

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 varchar,
username varchar);
insert into users (email_address, username) values ('j...@tanga.com',
'joevandyk');

select row_to_json(users) from users;
 {"id":1,"email_address":"j...@tanga.com","username":"joevandyk"}
  Correct, except that the "id" column is in the result.


select row_to_json(row(users.email_address, users.username)) from users;
 {"f1":"j...@tanga.com","f2":"joevandyk"}
   The column names are incorrect.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general