Re: [GENERAL] Slow Query - PostgreSQL 9.2

[Saulo Merlo]

Thank you!


"Limit  (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 
rows=0 loops=1)""  ->  Nested Loop Left Join  (cost=1557.00..4743.08 rows=1 
width=186) (actual time=0.050..0.050 rows=0 loops=1)""->  Nested Loop 
Left Join  (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 
rows=0 loops=1)""  ->  Nested Loop  (cost=519.01..824.17 rows=1 
width=114) (actual time=0.045..0.045 rows=0 loops=1)""->  
Nested Loop  (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 
rows=0 loops=1)""  ->  Nested Loop  
(cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 
loops=1)""->  Nested Loop  (cost=519.00..793.43 
rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)""   
   ->  Nested Loop  (cost=519.00..778.17 rows=1 width=98) 
(actual time=0.038..0.038 rows=0 loops=1)"" 
   ->  Nested Loop  (cost=519.00..762.91 rows=1 width=98) (actual 
time=0.036..0.036 rows=0 loops=1)"" 
 ->  Nested Loop  (cost=519.00..747.64 rows=1 width=98) (actual 
time=0.034..0.034 rows=0 loops=1)"" 
   Join Filter: (("t"."st_ino")::bigint = 
("fd"."st_ino_target")::bigint)""   
 ->  Nested Loop  (cost=519.00..732.38 rows=1 width=90) (actual 
time=0.031..0.031 rows=0 loops=1)"" 
 ->  Nested Loop  (cost=519.00..718.94 rows=1 width=98) (actual 
time=0.029..0.029 rows=0 loops=1)"" 
   Join Filter: (CASE WHEN ("sb"."constant_name" = 
'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" 
END IS NOT NULL)""  
  ->  Nested Loop  (cost=519.00..531.96 rows=1 width=20) (actual 
time=0.027..0.027 rows=0 loops=1)"" 
 Join Filter: 
((B''::"bit" & ("t"."st_mode")::"bit") = 
("sb"."bits")::"bit")"" 
 ->  Index Scan using "ix_inode_segments_nfs" on "inodes" "t"  
(cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)""  
  
Index Cond: (("st_ctime")::timestamp without time zone < 
("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" 
 ->  
CTE Scan on "stat_h_with_bits" "sb"  (cost=519.00..519.63 rows=23 width=72) 
(never executed)""  
  CTE stat_h""  
->  Values Scan on "*VALUES*"  (cost=0.00..0.29 
rows=23 width=68) (never executed)""
CTE stat_h_with_bits""  
->  CTE Scan on 
"stat_h" "s"  (cost=0.00..518.71 rows=23 width=68) (never executed)""   
 
SubPlan 6"" 
 ->  Aggregate  (cost=22.51..22.52 rows=1 width=32) (never 
executed)"" 
   ->  Function Scan on "regexp_split_to_table" 
"digits"  (cost=0.01..10.01 rows=1000 width=32) (never executed)""  
  ->  Index Scan using 
"ix_inode_segments_st_ino_targets" on "inode_segments" "p"  (cost=0.00..15.30 
rows=1 width=78) (never executed)"" 
 Index Cond: (("st_ino_target")::bigint = 
("t"."st_ino")::bigint)""   
 SubPlan 4""
  ->  Aggregate  (cost=171.66..171.67 rows=1 width=574) (never 
executed)"" 
   ->  Index Scan using "ix_inode_segments_st_inos" on "inode_segments" 
"fs"  (cost=0.00..171.55 rows=40 width=574) (never executed)""  
Index Cond: 
(("st_ino")::bigint = ("p"."st_ino_target")::bigint)""  
->  Index Scan using "pk_inodes" on 
"inodes" "i"  (cost=0.00..13.43 rows=1 width=8) (never executed)""

Re: [GENERAL] Slow Query - PostgreSQL 9.2

[Vitaly Burovoy]

On 1/10/16, Saulo Merlo  wrote:
> I've got a slow query.. I'd like to make it faster.. Make add an index?
> Query:
> SELECT j.clientid AS client_id,
>ni.segment_index AS note_id,
>f.inode_id AS file_id,
>f.node_full_path AS filename,
>f.last_changed AS date_created,
>f.file_data AS main_binary,
>medium.inode_id AS medium_id,
>medium.file_data AS medium_binary,
>thumbnail.inode_id AS thumbnail_id,
>thumbnail.file_data AS thumbnail_binary
> FROM gorfs.nodes AS f
> INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
> INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
> AND mv.segment_index = 'main.with_name'
> INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
> INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
> INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
> INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
> INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
> LEFT JOIN
>   (SELECT f.inode_id,
>   f.file_data,
>   fi.st_ino
>FROM gorfs.nodes AS f
>INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>AND mv.segment_index = 'medium.with_name'
>INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
> medium ON medium.st_ino = fn.st_ino_target
> LEFT JOIN
>   (SELECT f.inode_id,
>   f.file_data,
>   fi.st_ino
>FROM gorfs.nodes AS f
>INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>AND mv.segment_index = 'thumbnail.with_name'
>INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
> thumbnail ON thumbnail.st_ino = fn.st_ino_target
> WHERE f.file_data IS NOT NULL
>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed + '24
> months' :: INTERVAL)) LIMIT 100;

> <>
> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank you.

At least you can add an index:
CREATE INDEX ON gorfs.nodes(last_changed)

and rewrite part of WHERE clause to:
(f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
months'::INTERVAL))

It allows to decrease the slowest part of your query (sequence
scanning of a table, all 13.5M rows):
> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537 width=29) 
> (actual time=94987.224..94987.224 rows=0 loops=1)
>  Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) > 
> (("st_ctime")::timestamp without time zone + '2 years'::interval))

compare that time to the one in the topmost row of EXPLAIN:
> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual 
> time=94987.261..94987.261 rows=0 loops=1)

-- 
Best regards,
Vitaly Burovoy


-- 
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] Slow Query - PostgreSQL 9.2

[Vitaly Burovoy]

On 1/10/16, Vitaly Burovoy  wrote:
> On 1/10/16, Saulo Merlo  wrote:
>> I've got a slow query.. I'd like to make it faster.. Make add an index?
>> Query:
>> SELECT
>>   <>
>> FROM gorfs.nodes AS f
>>   <>
>> WHERE f.file_data IS NOT NULL
>>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed +
>> '24
>> months' :: INTERVAL)) LIMIT 100;
>
>> <>
>> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
>> you.
>
> At least you can add an index:
> CREATE INDEX ON gorfs.nodes(last_changed)
>
> and rewrite part of WHERE clause to:
> (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> months'::INTERVAL))
>
> It allows to decrease the slowest part of your query (sequence
> scanning of a table, all 13.5M rows):
>> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
>> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>>  Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>
> compare that time to the one in the topmost row of EXPLAIN:
>> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> time=94987.261..94987.261 rows=0 loops=1)

Hmm. It seems that gorfs.nodes is a view.
So creating index should be something like (I have no idea that schema
name for it):
CREATE INDEX ON _schema_name_.inodes(st_ctime)

-- 
Best regards,
Vitaly Burovoy


-- 
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] Fwd: dblink_connect fails

[James Sewell]

Oops forgot to reply back to this one in the Christmas shutdown.

It turned out in this (new) install on Windows Postgres was running as the
Network Service user (??).

This was causing the issue, changed to Postgres and I was all good.

Cheers,


James Sewell,
Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Thu, Dec 17, 2015 at 4:04 PM, Adrian Klaver 
wrote:

> On 12/16/2015 06:10 PM, James Sewell wrote:
>
>> Oops left off the list.
>>
>> -- Forwarded message --
>> From: *James Sewell* > >
>> Date: Thursday, 17 December 2015
>> Subject: dblink_connect fails
>> To: Joe Conway >
>>
>>
>> On Thu, Dec 17, 2015 at 12:11 PM, Joe Conway > > wrote:
>>
>> On 12/16/2015 04:53 PM, James Sewell wrote:
>> > No it is not.
>> >
>> > Just in case I tried setting it to 'postgres', logged in without -U
>> > (doesn't work without PGUSER set) and tried the operation again.
>>
>> > > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>>
>> That "PRDSWIDEGRID01$" is coming from somewhere in your environment.
>> Looks like it is supposed to be a variable of some sort which is
>> supposed to resolve to an actual postgres user but for some reason
>> doesn't.
>>
>> What about FDWs?
>>
>>
>> This is a new database with some tables and dblink loaded.
>>
>> The  PRDSWIDEGRID01 is actually the hostname - but I just can't see how
>> it's getting injected.
>>
>> My understanding was that psql -U should override? And also that any
>> user variable just sets the user PostgreSQL variable - which is postgres.
>>
>> Strange stuff. I've just tried from cygwin, from Windows psql and from
>> PGAdmin - all the same result.
>>
>
> That is all from the client point of view. As Joe Conway has mentioned you
> need to be looking from the server point of view. In other words what is
> the environment for the Postgres server you are running dblink_connect in?
>
>
>>
>>
>> --
>> Crunchy Data - http://crunchydata.com
>> PostgreSQL Support for Secure Enterprises
>> Consulting, Training, & Open Source Development
>>
>>
>>
>>
>>
>> --
>>
>> James Sewell,
>> PostgreSQL Team Lead / Solutions Architect
>> __
>>
>> Level 2, 50 Queen St, Melbourne VIC 3000
>>
>> *P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099
>>
>>
>> 
>> The contents of this email are confidential and may be subject to legal
>> or professional privilege and copyright. No representation is made that
>> this email is free of viruses or other defects. If you have received
>> this communication in error, you may not copy or distribute any part of
>> it or otherwise disclose its contents to anyone. Please advise the
>> sender of your incorrect receipt of this correspondence.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Things to notice (was Re: [GENERAL] Code of Conduct: Is it time?, broken thread I hope)

[Andrew Sullivan]

Someone (never mind who, this isn't intended to be a blame-game
message) wrote:

> Am I, as a mere male […]  :-)

Even with the smiley, _this_ is the sort of thing that causes
discussions to wander into hopeless weeds from which projects cannot
emerge.  I _know_ it is tempting to make this kind of remark.  But
it's not cool, it doesn't help, and it is exactly the sort of thing
that makes some people think CoCs are needed in the first place.

Suppose you were an uncertain young woman from a culture where men
have legal authority over you.  Suppose the only interaction with
programming peers you get is online.  (Yes, I know of at least one
such case personally.)  This sort of sarcastic remark, smiley or no,
causes you a new uncertainty.

Just be sensitive to the fact that the Internet is bigger than your
world, however big it is, and things will be better.  I am not a big
believer in written-down rules: I think mostly they're a fetishizing
of constitutional arrangements like those of the US and Canada (which
mostly don't work for those who are not already enfranchised).  But we
can do something about that by thinking about that possibility much
more than we can do something about it by writing down rules.

Still, the exercise of writing down rules may help to notice things
one wouldn't say to a friend.  And I hope we're all friends here.

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] Using xmax to detect deleted rows

[Jim Nasby]

On 1/8/16 9:02 AM, Alvaro Herrera wrote:

Meel Velliste wrote:

I would like to use the "xmax" column to detect rows that have been
recently deleted. Is it possible to get the deleted row versions with
non-zero xmax to remain visible long enough that I could periodically
check, say once an hour, and still be able to see rows that were deleted
since I last checked?


No.  Maybe you want a trigger that saves the deleted row somewhere (a
separate table perhaps) which you can later inspect and delete again?


If that is what you need then PgQ might be a good solution. But it'll be 
a lot simpler to just do whatever you need to do when the row is 
actually deleted. Just be sure you deal with rollbacks correctly if 
you're doing something external.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] Trigger function interface

[Jim Nasby]

On 1/7/16 6:15 PM, Tatsuo Ishii wrote:

On 1/6/16 7:03 PM, Tatsuo Ishii wrote:

Is it possible to get the parse tree in a C trigger function which is
invoked when DML (INSERT/UPDATE/DELETE against a view) is executed?


Yes, it's in fcinfo->flinfo->fn_expr.


Thanks for the info. But is this the parse tree for the top level
query which involves the trigger?


Hrm, apparently not. fcinfo->context would maybe be helpful, but I'm not 
sure.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] Recovery regression tests

[Kharage, Suraj]

Hello,

> The in-core TAP tests and pgTap are two independent things, the latter being 
> an independent facility and the in-core TAP tests do not need it. 
> If you want to run them, simply install the perl module IPC::Run and add this 
> configure switch: --enable-tap-tests.

That’s work for me. Thanks.

Regards
Suraj kharage
-Original Message-
From: Michael Paquier [mailto:michael.paqu...@gmail.com] 
Sent: 08 January 2016 18:11
To: Kharage, Suraj
Cc: PostgreSQL mailing lists
Subject: Re: [GENERAL] Recovery regression tests

On Fri, Jan 8, 2016 at 6:55 PM, kharagesuraj  wrote:
> I have postgres-9.6 dev source code.
> While executing regression tests(make check) for recovery i got 
> following message.
> "TAP tests not enabled"
>
> I have pgTap(0.95) installed.

The in-core TAP tests and pgTap are two independent things, the latter being an 
independent facility and the in-core TAP tests do not need it. If you want to 
run them, simply install the perl module IPC::Run and add this configure 
switch: --enable-tap-tests.
--
Michael

__
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.

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

[GENERAL] Slow Query - PostgreSQL 9.2

[Saulo Merlo]

I've got a slow query.. I'd like to make it faster.. Make add an index?
Query:
SELECT j.clientid AS client_id,
   ni.segment_index AS note_id,
   f.inode_id AS file_id,
   f.node_full_path AS filename,
   f.last_changed AS date_created,
   f.file_data AS main_binary,
   medium.inode_id AS medium_id,
   medium.file_data AS medium_binary,
   thumbnail.inode_id AS thumbnail_id,
   thumbnail.file_data AS thumbnail_binary
FROM gorfs.nodes AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN
  (SELECT f.inode_id,
  f.file_data,
  fi.st_ino
   FROM gorfs.nodes AS f
   INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
   INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
   AND mv.segment_index = 'medium.with_name'
   INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS 
medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN
  (SELECT f.inode_id,
  f.file_data,
  fi.st_ino
   FROM gorfs.nodes AS f
   INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
   INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
   AND mv.segment_index = 'thumbnail.with_name'
   INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS 
thumbnail ON thumbnail.st_ino = fn.st_ino_target
WHERE f.file_data IS NOT NULL
  AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed + '24 
months' :: INTERVAL)) LIMIT 100;EXPLAIN ANALYZE:
"Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual 
time=94987.261..94987.261 rows=0 loops=1)"
"  ->  Nested Loop Left Join  (cost=1556.99..579473097.84 rows=43410 width=186) 
(actual time=94987.257..94987.257 rows=0 loops=1)"
"->  Nested Loop Left Join  (cost=1038.00..483232645.16 rows=43410 
width=154) (actual time=94987.255..94987.255 rows=0 loops=1)"
"  ->  Nested Loop  (cost=519.00..409353299.84 rows=43410 
width=114) (actual time=94987.252..94987.252 rows=0 loops=1)"
"->  Nested Loop  (cost=519.00..409094090.84 rows=43410 
width=114) (actual time=94987.250..94987.250 rows=0 loops=1)"
"  ->  Nested Loop  (cost=519.00..408681283.16 
rows=43410 width=106) (actual time=94987.247..94987.247 rows=0 loops=1)"
"->  Nested Loop  (cost=519.00..407691740.11 
rows=64840 width=106) (actual time=94987.244..94987.244 rows=0 loops=1)"
"  ->  Nested Loop  
(cost=519.00..406213713.19 rows=96848 width=98) (actual 
time=94987.241..94987.241 rows=0 loops=1)"
"->  Nested Loop  
(cost=519.00..403641904.83 rows=191391 width=106) (actual 
time=94987.239..94987.239 rows=0 loops=1)"
"  Join Filter: (CASE WHEN 
("f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 
4) ELSE NULL::"bytea" END IS NOT NULL)"
"  ->  Nested Loop  
(cost=519.00..349935407.61 rows=287309 width=36) (actual 
time=94987.236..94987.236 rows=0 loops=1)"
"Join Filter: 
((B''::"bit" & ("t"."st_mode")::"bit") = 
("f"."bits")::"bit")"
"->  Nested Loop  
(cost=0.00..349819245.82 rows=287309 width=41) (actual 
time=94987.233..94987.233 rows=0 loops=1)"
"  ->  Nested Loop  
(cost=0.00..34326.71 rows=429140 width=41) (actual 
time=94987.231..94987.231 rows=0 loops=1)"
"->  Nested 
Loop  (cost=0.00..206165095.07 rows=8982354 width=41) (actual 
time=94987.228..94987.228 rows=0 loops=1)"
"  ->  
Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537 width=29) 
(actual time=94987.224..94987.224 rows=0 loops=1)"
"   
 Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) > 
(("st_ctime")::timestamp without time zone + '2 years'::interval))"
"   
 Rows Removed by Filter: 40683998"
"  ->  
Index Scan using "ix_inode_segments_st_ino_targets" on 

Re: [GENERAL] Code of Conduct: Is it time?

[Oleg Bartunov]

On Sun, Jan 10, 2016 at 9:44 PM, Regina Obe  wrote:

> > On 01/10/2016 08:07 AM, Bill Moran wrote:
>
> >> So, the purpose of a CoC is twofold:
> >>
> >> A) Define what "being excellent" means to this particular
> >> community.
> >> B) Provide a process for how to resolve things when "being
> >> excellent" doesn't happen.
> >>
> >> Without #1, nobody will want to do #2, as it's basically a
> >> job that can never be done correctly.
>
> > I agree with you completely. That is actually why I included the link to
> > the graphic in the last post. My point was, I have no intention of
> > having a CoC that is full of drivel. I would want a clear, concise,
> > no-B.S. CoC.
>
> > JD
>
> This may come as a big shock to many of you, but as a contributor
> I don't care if you are racist, sexist, transphobic or whatever as long as
> you
>
> 1) Are helpful when I ask a question
> 2) Stick to the topic
> 3) Don't get into petty etiquettes like "Please stop top posting"
> and if you really need to - A polite we prefer top posting would do
>
> 4) Are sensitive to people on other operating systems other than your
> preferred.
>

+1


>
>
> My other concern about CoCs is I fear someone is going to come and demand
> we change Master/Slave  to Leader/Follower, because Master is a male term
> and Slave is insensitive to grand-children of slaves.
>


Some people don't understand all these issues with she/he, for example, we
in Russia are not really concern about this.



>
>
> Thanks,
> Regina
>
>
>
>
>
> --
> 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] Slow Query - PostgreSQL 9.2

[Saulo Merlo]

Hi Vitaly,
Yep... gorfs.nodes is a view.
And the schema is: gorfs.inode_segments
So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
Is that correct? It would be "st_ctime"?
I've rewriten the query as well. Thank you for that!
Thank youLucas
> Date: Sun, 10 Jan 2016 21:23:01 -0800
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> From: vitaly.buro...@gmail.com
> To: smerl...@outlook.com
> CC: pgsql-general@postgresql.org
> 
> On 1/10/16, Vitaly Burovoy  wrote:
> > On 1/10/16, Saulo Merlo  wrote:
> >> I've got a slow query.. I'd like to make it faster.. Make add an index?
> >> Query:
> >> SELECT
> >>   <>
> >> FROM gorfs.nodes AS f
> >>   <>
> >> WHERE f.file_data IS NOT NULL
> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed +
> >> '24
> >> months' :: INTERVAL)) LIMIT 100;
> >
> >> <>
> >> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
> >> you.
> >
> > At least you can add an index:
> > CREATE INDEX ON gorfs.nodes(last_changed)
> >
> > and rewrite part of WHERE clause to:
> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> > months'::INTERVAL))
> >
> > It allows to decrease the slowest part of your query (sequence
> > scanning of a table, all 13.5M rows):
> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
> >>  Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
> >
> > compare that time to the one in the topmost row of EXPLAIN:
> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
> >> time=94987.261..94987.261 rows=0 loops=1)
> 
> Hmm. It seems that gorfs.nodes is a view.
> So creating index should be something like (I have no idea that schema
> name for it):
> CREATE INDEX ON _schema_name_.inodes(st_ctime)
> 
> -- 
> Best regards,
> Vitaly Burovoy
> 
> 
> -- 
> 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] Code of Conduct: Is it time?

[Michael Paquier]

On Mon, Jan 11, 2016 at 3:13 PM, Oleg Bartunov  wrote:
> Some people don't understand all these issues with she/he, for example, we
> in Russia are not really concern about this.

This depends on how the language is built. For example in French I
think it would matter (not living there for long though so perhaps my
perception is incorrect), and in Japanese it just doesn't matter,
there is no such concept.
-- 
Michael


-- 
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] Slow Query - PostgreSQL 9.2

[Vitaly Burovoy]

On 1/10/16, Saulo Merlo  wrote:
> Hi Vitaly,
>
> Yep... gorfs.nodes is a view.
> And the schema is: gorfs.inode_segments
> So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> Is that correct? It would be "st_ctime"?
If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
the above DDL is OK. According to EXPLAIN's "Filter" row the column
involving in comparison is st_ctime.

Hint: you can create the index without blocking table using "CREATE
INDEX CONCURRENTLY":
http://www.postgresql.org/docs/9.2/static/sql-createindex.html

> I've rewriten the query as well. Thank you for that!
>
> Thank you
> Lucas

>> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> From: vitaly.buro...@gmail.com
>> To: smerl...@outlook.com
>> CC: pgsql-general@postgresql.org
>>
>> On 1/10/16, Vitaly Burovoy  wrote:
>> > On 1/10/16, Saulo Merlo  wrote:
>> >> I've got a slow query.. I'd like to make it faster.. Make add an
>> >> index?
>> >> Query:
>> >> SELECT
>> >>   <>
>> >> FROM gorfs.nodes AS f
>> >>   <>
>> >> WHERE f.file_data IS NOT NULL
>> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed
>> >> +
>> >> '24
>> >> months' :: INTERVAL)) LIMIT 100;
>> >
>> >> <>
>> >> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
>> >> you.
>> >
>> > At least you can add an index:
>> > CREATE INDEX ON gorfs.nodes(last_changed)
>> >
>> > and rewrite part of WHERE clause to:
>> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
>> > months'::INTERVAL))
>> >
>> > It allows to decrease the slowest part of your query (sequence
>> > scanning of a table, all 13.5M rows):
>> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
>> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>> >>  Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>> >
>> > compare that time to the one in the topmost row of EXPLAIN:
>> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> >> time=94987.261..94987.261 rows=0 loops=1)
>>
>> Hmm. It seems that gorfs.nodes is a view.
>> So creating index should be something like (I have no idea that schema
>> name for it):
>> CREATE INDEX ON _schema_name_.inodes(st_ctime)

-- 
Best regards,
Vitaly Burovoy


-- 
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] Slow Query - PostgreSQL 9.2

[Saulo Merlo]

CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
ERROR:  column "st_ctime" does not exist
Look the error I've got
Lucas

> Date: Sun, 10 Jan 2016 22:43:21 -0800
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> From: vitaly.buro...@gmail.com
> To: smerl...@outlook.com
> CC: pgsql-general@postgresql.org
> 
> On 1/10/16, Saulo Merlo  wrote:
> > Hi Vitaly,
> >
> > Yep... gorfs.nodes is a view.
> > And the schema is: gorfs.inode_segments
> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> > Is that correct? It would be "st_ctime"?
> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
> the above DDL is OK. According to EXPLAIN's "Filter" row the column
> involving in comparison is st_ctime.
> 
> Hint: you can create the index without blocking table using "CREATE
> INDEX CONCURRENTLY":
> http://www.postgresql.org/docs/9.2/static/sql-createindex.html
> 
> > I've rewriten the query as well. Thank you for that!
> >
> > Thank you
> > Lucas
> 
> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> >> From: vitaly.buro...@gmail.com
> >> To: smerl...@outlook.com
> >> CC: pgsql-general@postgresql.org
> >>
> >> On 1/10/16, Vitaly Burovoy  wrote:
> >> > On 1/10/16, Saulo Merlo  wrote:
> >> >> I've got a slow query.. I'd like to make it faster.. Make add an
> >> >> index?
> >> >> Query:
> >> >> SELECT
> >> >>   <>
> >> >> FROM gorfs.nodes AS f
> >> >>   <>
> >> >> WHERE f.file_data IS NOT NULL
> >> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed
> >> >> +
> >> >> '24
> >> >> months' :: INTERVAL)) LIMIT 100;
> >> >
> >> >> <>
> >> >> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
> >> >> you.
> >> >
> >> > At least you can add an index:
> >> > CREATE INDEX ON gorfs.nodes(last_changed)
> >> >
> >> > and rewrite part of WHERE clause to:
> >> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> >> > months'::INTERVAL))
> >> >
> >> > It allows to decrease the slowest part of your query (sequence
> >> > scanning of a table, all 13.5M rows):
> >> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
> >> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
> >> >>  Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
> >> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
> >> >
> >> > compare that time to the one in the topmost row of EXPLAIN:
> >> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
> >> >> time=94987.261..94987.261 rows=0 loops=1)
> >>
> >> Hmm. It seems that gorfs.nodes is a view.
> >> So creating index should be something like (I have no idea that schema
> >> name for it):
> >> CREATE INDEX ON _schema_name_.inodes(st_ctime)
> 
> -- 
> Best regards,
> Vitaly Burovoy
> 
> 
> -- 
> 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] Slow Query - PostgreSQL 9.2

[Vitaly Burovoy]

On 1/10/16, Saulo Merlo  wrote:
> CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
> ERROR:  column "st_ctime" does not exist
> Look the error I've got
>
> Lucas
>
>> Date: Sun, 10 Jan 2016 22:43:21 -0800
>> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> From: vitaly.buro...@gmail.com
>> To: smerl...@outlook.com
>> CC: pgsql-general@postgresql.org
>>
>> On 1/10/16, Saulo Merlo  wrote:
>> > Hi Vitaly,
>> >
>> > Yep... gorfs.nodes is a view.
>> > And the schema is: gorfs.inode_segments
>> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
>> > Is that correct? It would be "st_ctime"?
>> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
>> the above DDL is OK. According to EXPLAIN's "Filter" row the column
>> involving in comparison is st_ctime.
>>
>> Hint: you can create the index without blocking table using "CREATE
>> INDEX CONCURRENTLY":
>> http://www.postgresql.org/docs/9.2/static/sql-createindex.html
>>
>> > I've rewriten the query as well. Thank you for that!
>> >
>> > Thank you
>> > Lucas
>>
>> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> >> From: vitaly.buro...@gmail.com
>> >> To: smerl...@outlook.com
>> >> CC: pgsql-general@postgresql.org
>> >>
>> >> On 1/10/16, Vitaly Burovoy  wrote:
>> >> > On 1/10/16, Saulo Merlo  wrote:
>> >> >> I've got a slow query.. I'd like to make it faster.. Make add an
>> >> >> index?
>> >> >> Query:
>> >> >> SELECT
>> >> >>   <>
>> >> >> FROM gorfs.nodes AS f
>> >> >>   <>
>> >> >> WHERE f.file_data IS NOT NULL
>> >> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') >
>> >> >> (f.last_changed
>> >> >> +
>> >> >> '24
>> >> >> months' :: INTERVAL)) LIMIT 100;
>> >> >
>> >> >> <>
>> >> >> "Total runtime: 94989.208 ms"What could I do to make it faster?
>> >> >> Thank
>> >> >> you.
>> >> >
>> >> > At least you can add an index:
>> >> > CREATE INDEX ON gorfs.nodes(last_changed)
>> >> >
>> >> > and rewrite part of WHERE clause to:
>> >> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
>> >> > months'::INTERVAL))
>> >> >
>> >> > It allows to decrease the slowest part of your query (sequence
>> >> > scanning of a table, all 13.5M rows):
>> >> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
>> >> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>> >> >>  Filter: ("timezone"('UTC'::"text", "transaction_timestamp"())
>> >> >> >
>> >> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>> >> >
>> >> > compare that time to the one in the topmost row of EXPLAIN:
>> >> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> >> >> time=94987.261..94987.261 rows=0 loops=1)
>> >>
>> >> Hmm. It seems that gorfs.nodes is a view.
>> >> So creating index should be something like (I have no idea that schema
>> >> name for it):
>> >> CREATE INDEX ON _schema_name_.inodes(st_ctime)

Please, post a definition of a table and a view (and all intermediate
views if any).

Via psql it can be done via:
\d gorfs.inode_segments
\d+ gorfs.nodes

-- 
Best regards,
Vitaly Burovoy


-- 
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] Slow Query - PostgreSQL 9.2

[Saulo Merlo]

gorgs.inode_segments:
-- Table: gorfs.inode_segments
-- DROP TABLE gorfs.inode_segments;
CREATE TABLE gorfs.inode_segments(  st_ino "gorfs"."ino_t" NOT NULL, -- Inode 
number the segment belongs to. alongside segment_index, it forms the table's 
primary key to ensure uniqueness per relevant scope  segment_index 
"gorfs"."pathname_component" NOT NULL, -- See st_no's column description for 
further details. The meaning of this column varies based on the host inode 
type:...  st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for 
directory inode segments (objects in the directory)  full_path 
"gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick 
lookups. Meaningful only for directory inode segments (objects in the 
directory)  segment_data "bytea", -- Actual data segment. Meaningful only for 
S_IFLNK and S_IFREG  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", 
"segment_index"),  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino) 
 REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE  ON UPDATE NO ACTION ON 
DELETE NO ACTION,  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY 
(st_ino_target)  REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE  ON 
UPDATE NO ACTION ON DELETE NO ACTION,  CONSTRAINT uc_no_duplicate_full_paths 
UNIQUE ("full_path"),  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK 
("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR 
"st_ino"::bigint = 2))WITH (  OIDS=FALSE);
S_IFSOCK:   0:   no data to store, no records hereS_IFLNK:1:   contains the 
link target (see columns comments for details).S_IFREG:0+:  actual data 
segments, up to 64MB each  (see columns comments for details)S_IFBLK:0:   
no data to store, no records hereS_IFDIR:0+:  one record per object name in 
the directoryS_IFCHR:0:   no data to store, no records hereS_IFIFO:0:   
no data to store, no records here';-- Index: gorfs.ix_inode_segments_climb_tree
-- DROP INDEX gorfs.ix_inode_segments_climb_tree;
CREATE INDEX ix_inode_segments_climb_tree  ON gorfs.inode_segments  USING btree 
 ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
-- Index: gorfs.ix_inode_segments_filter_by_subtree
-- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
CREATE INDEX ix_inode_segments_filter_by_subtree  ON gorfs.inode_segments  
USING btree  ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)  
WHERE "full_path" IS NOT NULL;COMMENT ON INDEX 
gorfs.ix_inode_segments_filter_by_subtree  IS 'Allows looking for left-anchored 
paths (either regex or LIKE).WARNING: as of 9.2 the index is not used when the 
comparison term is a non deterministic function (STABLE or VOLATILE).See 
http://www.postgresql.org/message-id/5451d6c4.7040...@vuole.me';
-- Index: gorfs.ix_inode_segments_full_path_resolution
-- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;
CREATE INDEX ix_inode_segments_full_path_resolution  ON gorfs.inode_segments  
USING btree  ("st_ino", "full_path" COLLATE pg_catalog."default");
-- Index: gorfs.ix_inode_segments_gsdi_pk
-- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;
CREATE INDEX ix_inode_segments_gsdi_pk  ON gorfs.inode_segments  USING btree  
(("st_ino"::"text") COLLATE pg_catalog."default", ("segment_index"::"text") 
COLLATE pg_catalog."default");
-- Index: gorfs.ix_inode_segments_ja_files_lookup
-- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;
CREATE INDEX ix_inode_segments_ja_files_lookup  ON gorfs.inode_segments  USING 
btree  ((CASEWHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN 
"upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 
'g'::"text"))ELSE NULL::"text"END) COLLATE pg_catalog."default")  WHERE 
"gorfs"."is_kaminski_note_path"("full_path"::"text");
-- Index: gorfs.ix_inode_segments_notes_clientids
-- DROP INDEX gorfs.ix_inode_segments_notes_clientids;
CREATE INDEX ix_inode_segments_notes_clientids  ON gorfs.inode_segments  USING 
btree  (("split_part"("full_path"::"text", '/'::"text", 4)::integer))  WHERE 
"gorfs"."is_kaminski_note_path"("full_path"::"text");
-- Index: gorfs.ix_inode_segments_notes_fileids
-- DROP INDEX gorfs.ix_inode_segments_notes_fileids;
CREATE INDEX ix_inode_segments_notes_fileids  ON gorfs.inode_segments  USING 
btree  (("split_part"("full_path"::"text", '/'::"text", 8)::integer))  WHERE 
"gorfs"."is_kaminski_note_path"("full_path"::"text");
-- Index: gorfs.ix_inode_segments_notes_noteids
-- DROP INDEX gorfs.ix_inode_segments_notes_noteids;
CREATE INDEX ix_inode_segments_notes_noteids  ON gorfs.inode_segments  USING 
btree  ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 
'unassigned'::"text")::integer))  WHERE 
"gorfs"."is_kaminski_note_path"("full_path"::"text");
-- Index: gorfs.ix_inode_segments_segment_indexes
-- DROP INDEX gorfs.ix_inode_segments_segment_indexes;
CREATE INDEX ix_inode_segments_segment_indexes  ON gorfs.inode_segments  USING 
btree  ("segment_index" COLLATE pg_catalog."default");
-- 

Re: [GENERAL] Slow Query - PostgreSQL 9.2

[Vitaly Burovoy]

On 1/10/16, Saulo Merlo  wrote:
> gorgs.inode_segments:
> <>
>
> gorfs.nodes:
> -- View: gorfs.nodes
> -- DROP VIEW gorfs.nodes;
> CREATE OR REPLACE VIEW gorfs.nodes AS
>  SELECT
> <>
> "t"."st_ctime" AS "last_changed", ...
> <>
>FROM "gorfs"."inode_segments" "p"
>JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = 
> "p"."st_ino"::bigint
>JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = 
> "p"."st_ino_target"::bigint
>JOIN "gorfs"."mode_t_flags"() "f"(...) ON ...
>LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = 
> "t"."media_subtype_id";
> <>

It seems alias for "t" is not "gorfs"."inode_segments" (it is "p"),
but "gorfs"."inodes" (in the second "LEFT JOIN" clause).
So, the correct DDL is:
CREATE INDEX CONCURRENTLY index_name ON gorfs.inodes(st_ctime);

P.S.: you can avoid "index_name" if the exact name is not important
for you. In such case name of the index will be constructed
automatically based on table name and column name(s).

>
>> Date: Sun, 10 Jan 2016 23:04:20 -0800
>> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> From: vitaly.buro...@gmail.com
>> To: smerl...@outlook.com
>> CC: pgsql-general@postgresql.org
>>
>> On 1/10/16, Saulo Merlo  wrote:
>> > CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
>> > ERROR:  column "st_ctime" does not exist
>> > Look the error I've got
>> >
>> > Lucas
>> >
>> >> Date: Sun, 10 Jan 2016 22:43:21 -0800
>> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> >> From: vitaly.buro...@gmail.com
>> >> To: smerl...@outlook.com
>> >> CC: pgsql-general@postgresql.org
>> >>
>> >> On 1/10/16, Saulo Merlo  wrote:
>> >> > Hi Vitaly,
>> >> >
>> >> > Yep... gorfs.nodes is a view.
>> >> > And the schema is: gorfs.inode_segments
>> >> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
>> >> > Is that correct? It would be "st_ctime"?
>> >> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
>> >> the above DDL is OK. According to EXPLAIN's "Filter" row the column
>> >> involving in comparison is st_ctime.
>> >>
>> >> Hint: you can create the index without blocking table using "CREATE
>> >> INDEX CONCURRENTLY":
>> >> http://www.postgresql.org/docs/9.2/static/sql-createindex.html
>> >>
>> >> > I've rewriten the query as well. Thank you for that!
>> >> >
>> >> > Thank you
>> >> > Lucas
>> >>
>> >> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> >> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> >> >> From: vitaly.buro...@gmail.com
>> >> >> To: smerl...@outlook.com
>> >> >> CC: pgsql-general@postgresql.org
>> >> >>
>> >> >> On 1/10/16, Vitaly Burovoy  wrote:
>> >> >> > On 1/10/16, Saulo Merlo  wrote:
>> >> >> >> I've got a slow query.. I'd like to make it faster.. Make add an
>> >> >> >> index?
>> >> >> >> Query:
>> >> >> >> SELECT
>> >> >> >>   <>
>> >> >> >> FROM gorfs.nodes AS f
>> >> >> >>   <>
>> >> >> >> WHERE f.file_data IS NOT NULL
>> >> >> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') >
>> >> >> >> (f.last_changed
>> >> >> >> +
>> >> >> >> '24
>> >> >> >> months' :: INTERVAL)) LIMIT 100;
>> >> >> >
>> >> >> >> <>
>> >> >> >> "Total runtime: 94989.208 ms"What could I do to make it faster?
>> >> >> >> Thank
>> >> >> >> you.
>> >> >> >
>> >> >> > At least you can add an index:
>> >> >> > CREATE INDEX ON gorfs.nodes(last_changed)
>> >> >> >
>> >> >> > and rewrite part of WHERE clause to:
>> >> >> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' -
>> >> >> > '24
>> >> >> > months'::INTERVAL))
>> >> >> >
>> >> >> > It allows to decrease the slowest part of your query (sequence
>> >> >> > scanning of a table, all 13.5M rows):
>> >> >> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24
>> >> >> >> rows=13416537
>> >> >> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>> >> >> >>  Filter: ("timezone"('UTC'::"text",
>> >> >> >> "transaction_timestamp"())
>> >> >> >> >
>> >> >> >> (("st_ctime")::timestamp without time zone + '2
>> >> >> >> years'::interval))
>> >> >> >
>> >> >> > compare that time to the one in the topmost row of EXPLAIN:
>> >> >> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> >> >> >> time=94987.261..94987.261 rows=0 loops=1)
>> >> >>
>> >> >> Hmm. It seems that gorfs.nodes is a view.
>> >> >> So creating index should be something like (I have no idea that
>> >> >> schema
>> >> >> name for it):
>> >> >> CREATE INDEX ON _schema_name_.inodes(st_ctime)
>>
>> Please, post a definition of a table and a view (and all intermediate
>> views if any).
>>
>> Via psql it can be done via:
>> \d gorfs.inode_segments
>> \d+ gorfs.nodes

-- 
Best regards,
Vitaly Burovoy


-- 
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] Slow Query - PostgreSQL 9.2

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Saulo Merlo
> Sent: Montag, 11. Januar 2016 08:12
> To: Vitaly Burovoy 
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> 
> gorgs.inode_segments:
> 
> 
>   -- Table: gorfs.inode_segments
> 
>   -- DROP TABLE gorfs.inode_segments;
> 
>   CREATE TABLE gorfs.inode_segments
>   (
> st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs 
> to. alongside segment_index, it forms
> the table's primary key to ensure uniqueness per relevant scope
> segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's 
> column description for further details.
> The meaning of this column varies based on the host inode type:...
> st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for 
> directory inode segments (objects in
> the directory)
> full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical 
> path for quick lookups. Meaningful
> only for directory inode segments (objects in the directory)
> segment_data "bytea", -- Actual data segment. Meaningful only for 
> S_IFLNK and S_IFREG
> CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
> CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
> REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
> REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
> CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" 
> IS NULL OR "st_ino"::bigint <>
> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
>   )
>   WITH (
> OIDS=FALSE
>   );

There is no field st_ctime.

> 
>   S_IFSOCK:   0:   no data to store, no records here
>   S_IFLNK:1:   contains the link target (see columns comments for 
> details).
>   S_IFREG:0+:  actual data segments, up to 64MB each  (see columns 
> comments for details)
>   S_IFBLK:0:   no data to store, no records here
>   S_IFDIR:0+:  one record per object name in the directory
>   S_IFCHR:0:   no data to store, no records here
>   S_IFIFO:0:   no data to store, no records here
>   ';
>   -- Index: gorfs.ix_inode_segments_climb_tree
> 
>   -- DROP INDEX gorfs.ix_inode_segments_climb_tree;
> 
>   CREATE INDEX ix_inode_segments_climb_tree
> ON gorfs.inode_segments
> USING btree
> ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
> 
>   -- Index: gorfs.ix_inode_segments_filter_by_subtree
> 
>   -- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
> 
>   CREATE INDEX ix_inode_segments_filter_by_subtree
> ON gorfs.inode_segments
> USING btree
> ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
> WHERE "full_path" IS NOT NULL;
>   COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
> IS 'Allows looking for left-anchored paths (either regex or LIKE).
>   WARNING: as of 9.2 the index is not used when the comparison term is a 
> non deterministic function (STABLE or
> VOLATILE).
>   See http://www.postgresql.org/message-id/5451d6c4.7040...@vuole.me
>   ';
> 
>   -- Index: gorfs.ix_inode_segments_full_path_resolution
> 
>   -- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;
> 
>   CREATE INDEX ix_inode_segments_full_path_resolution
> ON gorfs.inode_segments
> USING btree
> ("st_ino", "full_path" COLLATE pg_catalog."default");
> 
>   -- Index: gorfs.ix_inode_segments_gsdi_pk
> 
>   -- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;
> 
>   CREATE INDEX ix_inode_segments_gsdi_pk
> ON gorfs.inode_segments
> USING btree
> (("st_ino"::"text") COLLATE pg_catalog."default", 
> ("segment_index"::"text") COLLATE pg_catalog."default");
> 
>   -- Index: gorfs.ix_inode_segments_ja_files_lookup
> 
>   -- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;
> 
>   CREATE INDEX ix_inode_segments_ja_files_lookup
> ON gorfs.inode_segments
> USING btree
> ((
>   CASE
>   WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN 
> "upper"("regexp_replace"("full_path"::"text",
> '.*\.'::"text", ''::"text", 'g'::"text"))
>   ELSE NULL::"text"
>   END) COLLATE pg_catalog."default")
> WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
> 
>   -- Index: gorfs.ix_inode_segments_notes_clientids
> 
>   -- DROP INDEX gorfs.ix_inode_segments_notes_clientids;
> 
>   CREATE INDEX 

Re: [GENERAL] Code of Conduct: Is it time?

[Gavin Flower]

On 10/01/16 22:55, John R Pierce wrote:

On 1/9/2016 11:57 PM, Gavin Flower wrote:
I was at the 2015 Australian Linux Conference (held in Auckland, NZ), 
when Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus 
remained calm and polite throughout, yet most people would have been 
obviously annoyed within the first 5 minutes. 


(total outsider here, looking in)

some people are just toxic.   psychic vampires.  They can suck all the 
energy out of something while contributing little or nothing.


OTOH, she seems to have done some seriously good work, hard stuff like 
pioneering the linux framework for USB 3.0.


The more I read, the more I'm at least somewhat on her side, Linus 
does not need to be as much of an a**hole as he comes off as. For sure 
dealing with an environment like that you need to be really thick 
skinned.At times when I read about Linus and the whole kernel 
environment I think he's a vampire, but he's taking the power he's 
sucking up and building something, so maybe thats excusable... does he 
really need to be /that/ big of an ahole?  I dunno.



entirely on the other hand, I note that FreeBSD development has a 
whole lot less drama, and at least in my opinion, the kernel is a 
whole lot more stable.   h.




I sometimes look at the kernel mailing list:
 https://lkml.org
Linus is normally very mild tempered, rarely do I see him lash out, but 
I've only seen that against people who are competent, but doing/saying 
something Linus strongly disagrees with.  Most times he disagrees in an 
almost boringly mild way.


I would be quiet chuffed if Linus was rude to me - as that would mean 
that I'd met a fairly high standard.  If I sent in a really stupid 
patch, it would simply be ignored.  Though I must say, I've not, and 
almost certainly never going to, send in a kernel patch!


In the early days, Linus would quite readily admit to doing something 
stupid & suggest that he should wear a brown paper bag in shame!  He has 
a wonderful sense of humour, especially apparent in the early days of 
Linux - but his kernel release comments now appear far too professional!


Linus had said that one time he was too polite, and a developer 
persisted wasting a lot of effort before Linus could get through to 
him.  So Linus is now a lot more direct.


Sarah is an extremely brilliant and very productive kernel programmer, 
out classes me many times over in all programming metrics of any value - 
it is a grave pity that she takes comments as personal attacks.


I have immense respect for Linus, and I understand where he is coming from.

I unexpectedly had about a ten minute one-to-one conversation with him 
at the 2015 conference.  He is aware that he is far from perfect.  I 
would be very happy if I was at least 1% as he is, in terms of effective 
ability and contributions.


I suspect that Linux is more capable and growing a lot faster than any 
of the BSD's!!!  Though the BSD's may be more stable.



Cheers,
Gavin


--
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] Code of Conduct: Is it time?

[Regina Obe]

> I was at the 2015 Australian Linux Conference (held in Auckland, NZ), when
Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus remained
calm and polite throughout, yet most people would have been obviously
annoyed within the first 5 minutes.  As backround see: 
>
http://www.networkworld.com/article/2988850/opensource-subnet/linux-kernel-d
ev-sarah-sharp-quits-citing-brutal-communications-style.html

> I think some people, unintentionally, set themselves up as a victim.

> So I would agree that a Coc is likely only to lead to arguments. Take
something innocuous like 'do not offend people' - sounds good, now politely
explain why someone's deeply held beliefs contradict reality!


> Cheers,
> Gavin


Sarah is my most favorite person in the world.  I made critical comments on
her blog once when she went crazy on Linus which she deleted. I must be a
troll. I see now she's into doing stats on the people she deleted comments
of.  

http://sarah.thesharps.us/2016/01/07/metrics-of-haters/

Maybe we should suggest she should use PostgreSQL for that and demonstrate
our fancy stat functions.  

She won Red Hat Woman of the Year Award -
https://www.redhat.com/en/about/women-in-open-source

Sarah Sharp
2015 Community Award winner

Am I the only one concerned about some of the women role models we have in
FOSS?

Thanks,
Regina







-- 
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] Code of Conduct: Is it time?

[John R Pierce]

On 1/9/2016 11:57 PM, Gavin Flower wrote:
I was at the 2015 Australian Linux Conference (held in Auckland, NZ), 
when Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus 
remained calm and polite throughout, yet most people would have been 
obviously annoyed within the first 5 minutes. 


(total outsider here, looking in)

some people are just toxic.   psychic vampires.  They can suck all the 
energy out of something while contributing little or nothing.


OTOH, she seems to have done some seriously good work, hard stuff like 
pioneering the linux framework for USB 3.0.


The more I read, the more I'm at least somewhat on her side, Linus does 
not need to be as much of an a**hole as he comes off as.   For sure 
dealing with an environment like that you need to be really thick 
skinned.At times when I read about Linus and the whole kernel 
environment I think he's a vampire, but he's taking the power he's 
sucking up and building something, so maybe thats excusable... does he 
really need to be /that/ big of an ahole?  I dunno.



entirely on the other hand, I note that FreeBSD development has a whole 
lot less drama, and at least in my opinion, the kernel is a whole lot 
more stable.   h.



--
john r pierce, reusing bits in santa cruz



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

[GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

Hi all.
 
I'm planning to move all my pg_largeobject tables to separate tablespaces and 
to be able to do that I need to shuddown PG and start in single-user mode, like 
this:
 
postgres --single -O -D $PGDATA $DB_NAME
 
Then I have to execute the command:
alter table pg_largeobject set tablespace some_tablespace;
 
Is it possible to issue the above ALTER-statement from the command-line so I 
can start PG in single-user mode AND execute the ALTER-statement from the 
command-line i a bash-loop?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] Code of Conduct: Is it time?

[John R Pierce]

On 1/9/2016 11:37 PM, Regina Obe wrote:

Josh informed me you guys are thinking about a CoC.  Let me start off by
saying that I don't think you need one and in fact having one may be
dangerous.  I fear for your safety.


indeed.  I think this man said it best.

https://youtu.be/PjVbypiUOHA?t=35s







--
john r pierce, recycling bits in santa cruz



--
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] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane >:
Andreas Joseph Krogh  writes:
 > I'm planning to move all my pg_largeobject tables to separate tablespaces 
and
 > to be able to do that I need to shuddown PG and start in single-user mode, 
like
 > this:
 > postgres --single -O -D $PGDATA $DB_NAME
 > Then I have to execute the command:
 > alter table pg_largeobject set tablespace some_tablespace;

 Why do you think you need single-user mode for that?
 
Because of this?
 
ERROR:  permission denied: "pg_largeobject" is a system catalog

 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Code of Conduct: Is it time?

[Bill Moran]

On Sun, 10 Jan 2016 07:36:23 -0800
"Joshua D. Drake"  wrote:

> Hey,
> 
> For the record, my thoughts on a CoC are something like:
> 
> 1. Be excellent to each other
> 2. If you don't know what that means, leave
> 3. If someone isn't being excellent please contact: XYZ
> 
> With XYZ being a committee that determines the ABCs.

In general, I agree; but there are problems with 1 and 2.

The definition of "being excellent" varies from individual
to individual; but more importantly, from culture to culture.
As a result, pretty much everyone would have to leave as a
result of #2, because very few people know what "being
excellent" means to everyone involved.

As a result, I would feel REALLY bad for XYZ, who would be
put in the unenviable place of trying to mitigate disputes
with no guidance whatsoever.

So, the purpose of a CoC is twofold:

A) Define what "being excellent" means to this particular
   community.
B) Provide a process for how to resolve things when "being
   excellent" doesn't happen.

Without #1, nobody will want to do #2, as it's basically a
job that can never be done correctly.

But defining #1 is the really difficult part, because no matter
how you define it, there will be some people who disagree with
said definition.

The fact that Postgres has not needed a CoC up till now is a
testiment to the quality of the people in the community. However,
if Postgres continues to be more popular, the number of people
involved is going to increase. Simply as a factor of statistics,
the project will be forced to deal with some unsavory people at
some point. Having a CoC is laying the foundation to ensure that
dealing with those people involves the least pain possible. It
will always involve _some_ pain, but less is better.

I've done the job of #3 with other groups, and 99% of the time
there was nothing to do. The one incident I had to handle was
terrible, but at least I had some guidance on how to deal with
it.

-- 
Bill Moran


-- 
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] Execute commands in single-user mode

[Melvin Davidson]

Well, you can't combine psql with the postgres startup, but you can issue
subsequent commands from bash with the -c option:

EG:

postgres --single -O -D $PGDATA $DB_NAME
# give postgres a few seconds to complete startup
sleep 30
psql -U postgres -d your_database -c "alter table pg_largeobject set
tablespace some_tablespace;"

pg_ctl stop -d $PGDATA -m fast

BTW, it's always good form to state the exact O/S and version of
PostgreSQL, regardless of whether you think it may apply.


On Sun, Jan 10, 2016 at 8:53 AM, Andreas Joseph Krogh 
wrote:

> Hi all.
>
> I'm planning to move all my pg_largeobject tables to separate tablespaces
> and to be able to do that I need to shuddown PG and start in single-user
> mode, like this:
>
> postgres --single -O -D $PGDATA $DB_NAME
>
> Then I have to execute the command:
> alter table pg_largeobject set tablespace some_tablespace;
>
> Is it possible to issue the above ALTER-statement from the command-line so
> I can start PG in single-user mode AND execute the ALTER-statement from the
> command-line i a bash-loop?
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Execute commands in single-user mode

[Melvin Davidson]

Sorry, I've never used single user mode,
but here is a better example

#!/bin/bash
echo "**CHANGING TABLESPACES**"
gosu postgres postgres --single -O -D $PGDATA $DB_NAME<<- EOSQL
   alter table pg_largeobject set tablespace some_tablespace;
EOSQL
pg_ctl stop -d $PGDATA -m fast
echo ""
echo "**TABLESPACES CHANGED**"

fyi, revised from
http://stackoverflow.com/questions/28244869/creating-a-table-in-single-user-mode-in-postgres

On Sun, Jan 10, 2016 at 10:01 AM, Andreas Joseph Krogh 
wrote:

> På søndag 10. januar 2016 kl. 15:52:12, skrev Melvin Davidson <
> melvin6...@gmail.com>:
>
> Well, you can't combine psql with the postgres startup, but you can issue
> subsequent commands from bash with the -c option:
>
> EG:
>
> postgres --single -O -D $PGDATA $DB_NAME
> # give postgres a few seconds to complete startup
> sleep 30
> psql -U postgres -d your_database -c "alter table pg_largeobject set
> tablespace some_tablespace;"
>
> pg_ctl stop -d $PGDATA -m fast
>
>
> How can that work?
>
> Starting in single-mode gives you a prompt, so there's no way to issue
> another command. Even if it was, PG is not accepting connections, so using
> psql won't work, unless I'm misstaken...
>
>
> BTW, it's always good form to state the exact O/S and version of
> PostgreSQL, regardless of whether you think it may apply.
>
>
> Yea, sorry, it's 9.5.0
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Execute commands in single-user mode

[Tom Lane]

Andreas Joseph Krogh  writes:
> I'm planning to move all my pg_largeobject tables to separate tablespaces and 
> to be able to do that I need to shuddown PG and start in single-user mode, 
> like 
> this:
> postgres --single -O -D $PGDATA $DB_NAME
> Then I have to execute the command:
> alter table pg_largeobject set tablespace some_tablespace;

Why do you think you need single-user mode for that?

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] Code of Conduct: Is it time?

[Joshua D. Drake]

Hey,

For the record, my thoughts on a CoC are something like:

1. Be excellent to each other
2. If you don't know what that means, leave
3. If someone isn't being excellent please contact: XYZ

With XYZ being a committee that determines the ABCs.

Or in other words something like this (without the profanity):

https://s-media-cache-ak0.pinimg.com/564x/9e/95/2f/9e952f5fadae057840e549779f4309c7.jpg

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 15:52:12, skrev Melvin Davidson <
melvin6...@gmail.com >:
Well, you can't combine psql with the postgres startup, but you can issue 
subsequent commands from bash with the -c option:
  
 EG:

 postgres --single -O -D $PGDATA $DB_NAME
# give postgres a few seconds to complete startup
sleep 30
 psql -U postgres -d your_database -c "alter table pg_largeobject set 
tablespace some_tablespace;"
  
pg_ctl stop -d $PGDATA -m fast


 
How can that work?
 
Starting in single-mode gives you a prompt, so there's no way to issue another 
command. Even if it was, PG is not accepting connections, so using psql won't 
work, unless I'm misstaken...
  
BTW, it's always good form to state the exact O/S and version of PostgreSQL, 
regardless of whether you think it may apply.
 

Yea, sorry, it's 9.5.0
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Execute commands in single-user mode

[Tom Lane]

Andreas Joseph Krogh  writes:
> På søndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane  >:
> Andreas Joseph Krogh  writes:
>>> Then I have to execute the command:
>>> alter table pg_largeobject set tablespace some_tablespace;

>  Why do you think you need single-user mode for that?

> Because of this?
> ERROR:  permission denied: "pg_largeobject" is a system catalog

What that means is that you need allow_system_table_mods to be set.
It's a postmaster-start-time GUC, but still just a GUC.

So, assuming that you want to use psql to enter your commands,
you'd do something like this:

1. Add allow_system_table_mods=on to postgresql.conf.
2. Restart postmaster.
3. Issue ALTER TABLE commands.
4. Remove allow_system_table_mods setting from postgresql.conf.
5. Restart postmaster.

Prudence would suggest that you also alter pg_hba.conf to prevent
anyone but yourself from connecting to the postmaster while it's
in this state.  But that's optional.

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] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 16:53:54, skrev Tom Lane >:
Andreas Joseph Krogh  writes:
 > P�� s��ndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane  >:
 > Andreas Joseph Krogh  writes:
 >>> Then I have to execute the command:
 >>> alter table pg_largeobject set tablespace some_tablespace;

 >  Why do you think you need single-user mode for that?

 > Because of this?
 > ERROR:  permission denied: "pg_largeobject" is a system catalog

 What that means is that you need allow_system_table_mods to be set.
 It's a postmaster-start-time GUC, but still just a GUC.

 So, assuming that you want to use psql to enter your commands,
 you'd do something like this:

 1. Add allow_system_table_mods=on to postgresql.conf.
 2. Restart postmaster.
 3. Issue ALTER TABLE commands.
 4. Remove allow_system_table_mods setting from postgresql.conf.
 5. Restart postmaster.

 Prudence would suggest that you also alter pg_hba.conf to prevent
 anyone but yourself from connecting to the postmaster while it's
 in this state.  But that's optional.

 regards, tom lane
 
Aha, thanks!
 
pg_largeobject being a system-relation does quite make sense to me, but that's 
another discussion. I know there has been some discussions in the past about 
making it a non system-relation but it never got anywhere AFAIK.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Code of Conduct: Is it time?

[Joshua D. Drake]

On 01/10/2016 08:07 AM, Bill Moran wrote:


So, the purpose of a CoC is twofold:

A) Define what "being excellent" means to this particular
community.
B) Provide a process for how to resolve things when "being
excellent" doesn't happen.

Without #1, nobody will want to do #2, as it's basically a
job that can never be done correctly.


I agree with you completely. That is actually why I included the link to 
the graphic in the last post. My point was, I have no intention of 
having a CoC that is full of drivel. I would want a clear, concise, 
no-B.S. CoC.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Code of Conduct: Is it time?

[Regina Obe]

> On 01/10/2016 08:07 AM, Bill Moran wrote:

>> So, the purpose of a CoC is twofold:
>>
>> A) Define what "being excellent" means to this particular
>> community.
>> B) Provide a process for how to resolve things when "being
>> excellent" doesn't happen.
>>
>> Without #1, nobody will want to do #2, as it's basically a
>> job that can never be done correctly.

> I agree with you completely. That is actually why I included the link to
> the graphic in the last post. My point was, I have no intention of
> having a CoC that is full of drivel. I would want a clear, concise,
> no-B.S. CoC.

> JD

This may come as a big shock to many of you, but as a contributor
I don't care if you are racist, sexist, transphobic or whatever as long as
you

1) Are helpful when I ask a question
2) Stick to the topic
3) Don't get into petty etiquettes like "Please stop top posting"
and if you really need to - A polite we prefer top posting would do

4) Are sensitive to people on other operating systems other than your
preferred.

One thing that really pushes my buttons is when I ask for help as a windows
user and some person makes a snide remark about why don't I switch to
Linux - problem solved
Or because I'm on windows, I don't care about performance.

Here is an example thread I recall from a while back on PostGIS list.

https://lists.osgeo.org/pipermail/postgis-users/2008-June/020331.html

In PostGIS group people are very good at calling out other people when
they think they've said something mean-spirited
and I think people are greatful for being called out because the nasty
person had no idea
their joke was mean.

My other concern about CoCs is I fear someone is going to come and demand
we change Master/Slave  to Leader/Follower, because Master is a male term
and Slave is insensitive to grand-children of slaves.


Thanks,
Regina





-- 
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] Code of Conduct: Is it time?

[Gavin Flower]

On 11/01/16 07:44, Regina Obe wrote:
[...]

This may come as a big shock to many of you, but as a contributor
I don't care if you are racist, sexist, transphobic or whatever as long as
you

1) Are helpful when I ask a question
2) Stick to the topic
3) Don't get into petty etiquettes like "Please stop top posting"
and if you really need to - A polite we prefer top posting would do

4) Are sensitive to people on other operating systems other than your
preferred.
I once went out of my way to help someone with Mac.  They were so Mac 
centric they did not realize that they were not giving us the right 
information to help them, but this was not obvious until later in the 
thread.  I made some comment about Linux - next moment they were 
accusing everyone of not helping them properly because they were using a 
Mac, as though we had been deliberately discriminating against them!


So yes, I am sensitive to the O/S people are using, I will now avoid 
helping people who don't use Linux.  As I may not understand their needs 
properly, and I don't want to be accused of picking on them because they 
not using the "RIGHT OPERATING SYSTEM"!  But I have neither the time nor 
the expertise to even help everyone who uses Linux, even if they DO use 
the "ONE TRUE LINUX DISTRIBUTION" (being very careful not to mention the 
distribution I'm using - not wanting to start a flame war!!!).


I've twice been effective in supporting people with programs written in 
BASIC, were the version of BASIC was unfamiliar to me and I could not 
test my suggested change because they used a Microsoft O/S and I did not 
have access to any Microsoft boxen for testing purposes (at the time).  
In a recent project, I even ran a Microsoft O/S in a VM on my Linux box 
to test something for a project I was leading.  So I don't have an 
overriding religious type objections to helping people with other 
operating systems!


One thing that really pushes my buttons is when I ask for help as a windows
user and some person makes a snide remark about why don't I switch to
Linux - problem solved
Or because I'm on windows, I don't care about performance.

Here is an example thread I recall from a while back on PostGIS list.

https://lists.osgeo.org/pipermail/postgis-users/2008-June/020331.html

In PostGIS group people are very good at calling out other people when
they think they've said something mean-spirited
and I think people are greatful for being called out because the nasty
person had no idea
their joke was mean.

My other concern about CoCs is I fear someone is going to come and demand
we change Master/Slave  to Leader/Follower, because Master is a male term
and Slave is insensitive to grand-children of slaves.


Comrades, we are all equal! So to set one program above another is an 
anathema!  :-)



Thanks,
Regina






Chers,
Gavin


--
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] Code of Conduct: Is it time?

[Joshua D. Drake]

On 01/10/2016 10:44 AM, Regina Obe wrote:


JD


This may come as a big shock to many of you, but as a contributor
I don't care if you are racist, sexist, transphobic or whatever as long as
you


I think this is reasonable but my point is that we don't care if you are 
sexist (in terms of .Org). We care if you allow your sexism to bleed 
into the community.


In short, as long as you are professional and respectful, your personal 
beliefs may remain your own.


JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Code of Conduct: Is it time?

[Andrew Sullivan]

On Sun, Jan 10, 2016 at 01:44:37PM -0500, Regina Obe wrote:
> 1) Are helpful when I ask a question
> 2) Stick to the topic
> 3) Don't get into petty etiquettes like "Please stop top posting"
> and if you really need to - A polite we prefer top posting would do
> 
> 4) Are sensitive to people on other operating systems other than your
> preferred.

That seems like a pretty good scratch CoC to me.  (See my other note
about how other communities deal with this.)  It's concrete, short, to
the point, and a useful thing to point to when some flamewar breaks
out over irrelevant stuff.  If people want a CoC, I think it should be
something like the above.

> My other concern about CoCs is I fear someone is going to come and demand
> we change Master/Slave  to Leader/Follower, because Master is a male term
> and Slave is insensitive to grand-children of slaves.

If someone did that, it would fall under (2), no?  (I note that a
recent RFC, of which I am a co-author, about DNS terminology did say
that "primary" and "secondary" were to be preferred over "master" and
"slave".  I didn't personally agree with the claim, but that's what
got consensus.)

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] Code of Conduct: Is it time?

[Tim Clarke]

On 10/01/16 18:44, Regina Obe wrote:
>
> This may come as a big shock to many of you, but as a contributor
> I don't care if you are racist, sexist, transphobic or whatever as long as
> you
>
> 1) Are helpful when I ask a question
> 2) Stick to the topic
> 3) Don't get into petty etiquettes like "Please stop top posting"
> and if you really need to - A polite we prefer top posting would do
>
> 4) Are sensitive to people on other operating systems other than your
> preferred.
>
> One thing that really pushes my buttons is when I ask for help as a windows
> user and some person makes a snide remark about why don't I switch to
> Linux - problem solved
> Or because I'm on windows, I don't care about performance.
>
> Here is an example thread I recall from a while back on PostGIS list.
>
> https://lists.osgeo.org/pipermail/postgis-users/2008-June/020331.html
>
> In PostGIS group people are very good at calling out other people when
> they think they've said something mean-spirited
> and I think people are greatful for being called out because the nasty
> person had no idea
> their joke was mean.
>
> My other concern about CoCs is I fear someone is going to come and demand
> we change Master/Slave  to Leader/Follower, because Master is a male term
> and Slave is insensitive to grand-children of slaves.
>
>
> Thanks,
> Regina

+1

-- 
Tim Clarke


-- 
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] Execute commands in single-user mode

[Jim Nasby]

On 1/10/16 10:01 AM, Andreas Joseph Krogh wrote:

pg_largeobject being a system-relation does quite make sense to me, but
that's another discussion. I know there has been some discussions in the
past about making it a non system-relation but it never got anywhere AFAIK.


BTW, there's some other issues with large objects, notably their use of 
OIDs. Lots of LOs can lead to OID depletion. There was a thread about 
this recently.


It might be about time to come up with an extension that's a replacement 
for large objects.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] Code of Conduct: Is it time?

[Regina Obe]

>> She won Red Hat Woman of the Year Award -
>> https://www.redhat.com/en/about/women-in-open-source
>>
>> Sarah Sharp
>> 2015 Community Award winner
>>
>> Am I the only one concerned about some of the women role models we have
in
>> FOSS?
> Am I, as a mere male, entitled to have an opinion on this?  :-)

> It is very sad, that some people would answer the above question with a 
> resounding no!

I think anybody who has a vested interest in FOSS and in a project has the
right to say 
"I think we are sending the wrong message to the younger generation what it
takes to succeed in Open Source"

I personally feel that we are sending the message

1) Find the person who has contributed the most to a project, 
2) Select some choice pieces out of the 10,000 emails they have written that
suggests they are a jerk
3) Show this to the world and say "You see, your hero is a big jerk"
4) Have your friends twit and blog the message until everyone thinks this
guy is a big jerk.
5) Write up a lengthy painful to read doctrine about how you are going to
bring peace to the project.
6) Broadcast how your doctrine has made the project a more civil place for
discuss

BTW - I rarely try to get into things like discussing Cocs and stuff, but
this time I am just fed up with having my concentration ruined
when I could be devoting precious time to my projects.

This is causing me severe emotional distress.

Thanks,
Regina




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

[GENERAL] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

When executing a query using \watch in psql the first execution of the
query includes "Title is [...]" when \pset title is in use.  Subsequent
executions do not.  Once that first display goes off-screen the information
in the title is no longer readily accessible.  If using \watch for a
long-running monitoring query it can be helpful to incorporate some context
information into the title.

Does anyone capable agree enough to implement this?

Any suggestions for a better way to accomplish the goal?

I have a watch query whose title incorporates the name of the libpq service
name (via bash variables on Linux) so that I can verify which database it
is monitoring without having to kill the watched command and inspect the
prompt, command line, or process.

Working with 9.3 presently.

Thanks!

David J.

Re: [GENERAL] Code of Conduct: Is it time?

[Gavin Flower]

On 10/01/16 21:31, Regina Obe wrote:

I was at the 2015 Australian Linux Conference (held in Auckland, NZ), when

Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus remained
calm and polite throughout, yet most people would have been obviously
annoyed within the first 5 minutes.  As backround see:
http://www.networkworld.com/article/2988850/opensource-subnet/linux-kernel-d
ev-sarah-sharp-quits-citing-brutal-communications-style.html


I think some people, unintentionally, set themselves up as a victim.
So I would agree that a Coc is likely only to lead to arguments. Take

something innocuous like 'do not offend people' - sounds good, now politely
explain why someone's deeply held beliefs contradict reality!



Cheers,
Gavin


Sarah is my most favorite person in the world.  I made critical comments on
her blog once when she went crazy on Linus which she deleted. I must be a
troll. I see now she's into doing stats on the people she deleted comments
of.
I initially heard about Sarah when I read an item about her being the 
first to implement USB 3 support, and it was for Linux!  So I started 
off having tremendous respect for her.  While I have programmed at the 
assembly level for 3 different types of processors many years ago, I am 
certain she is considerably more competent than I ever was technically.




http://sarah.thesharps.us/2016/01/07/metrics-of-haters/

I had a look at this.

While there obviously were some comments that I and most others would 
utterly condemn - there was no breakdown of other comments. So no way of 
knowing if she considered all criticism of her as being hateful.




Maybe we should suggest she should use PostgreSQL for that and demonstrate
our fancy stat functions.

She won Red Hat Woman of the Year Award -
https://www.redhat.com/en/about/women-in-open-source

Sarah Sharp
2015 Community Award winner

Am I the only one concerned about some of the women role models we have in
FOSS?

Am I, as a mere male, entitled to have an opinion on this?  :-)

It is very sad, that some people would answer the above question with a 
resounding no!




Thanks,
Regina









--
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] COPY FROM STDIN

[Jim Nasby]

On 1/8/16 10:37 AM, Luke Coldiron wrote:

On 1/6/16 9:45 PM, Luke Coldiron wrote:

In the example above I'm not sure if I can use some sub struct of the
SPIPlanPtr and hand it off to the DoCopy function as the CopyStmt or
if I need to go about this entirely different. Any advice on the
matter would be much appreciated.


I don't know off-hand. I suggest you look at what psql does to implement

\copy (note the \).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in

Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in
Treble! http://BlueTreble.com

I took a look at the \copy but as far as I can tell this is using the
frontend (client side) of postgresql and I need to be able to do this on the


Oops.


backend (server side). I don't see a way with this either to attach to the
connection that called the c function and execute the copy statement. The
DoCopy in commands/copy.h appears to me to be the server side copy command.
And I think I can get it to work if I can figure out how to attach to the
connection of the calling function and pass in the CopyStmt variable.


That sounds... bad.

I think the way to handle this is to add a new type to CopyDest and 
modify CopyGetData() and CopySendEndOfRow() accordingly.


It might be tempting to use CopyState->filename as the pointer to a 
StringInfoData (StringInfo), but I'm not sure that's a great idea. I 
think it'd be better to add a new field to CopyStateData.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 22:38:05, skrev Jim Nasby <
jim.na...@bluetreble.com >:
On 1/10/16 10:01 AM, Andreas Joseph Krogh wrote:
 > pg_largeobject being a system-relation does quite make sense to me, but
 > that's another discussion. I know there has been some discussions in the
 > past about making it a non system-relation but it never got anywhere AFAIK.

 BTW, there's some other issues with large objects, notably their use of
 OIDs. Lots of LOs can lead to OID depletion. There was a thread about
 this recently.

 It might be about time to come up with an extension that's a replacement
 for large objects.
 --
 Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
 Experts in Analytics, Data Architecture and PostgreSQL
 Data in Trouble? Get it in Treble! http://BlueTreble.com
 
What would it take to fund such an extension?
 
It would "have to" support:
- Migrate existing LOs away from pg_largeobject
- Proper driver-support (JDBC)
- Possible to exclude from pg_dump
- Support pg_upgrade
 
And -hackers should agree on the goal to ultimately being merged into core and 
replace pg_largeobject.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Code of Conduct: Is it time?

[Adrian Klaver]

On 01/10/2016 02:05 PM, Regina Obe wrote:

Gavin,

I once went out of my way to help someone with Mac.  They were so Mac

centric they did not realize that they were not giving us the right
information to help them, but this was not obvious until later in the
thread.  I made some comment about Linux - next moment they were accusing

everyone of not helping them properly because they were using a Mac, as

though we had been deliberately discriminating against them!

I hear ya. I do the same thing setting up a Linux VM to try to help someone
on Ubuntu or CentOS.
My main point was if you don't have anything helpful to say, don't say
anything at all.

I recall someone posting something earlier about on the lists we should have
a section like:

HELP US HELP YOU

That details the information anyone having a problem should provide to make
it easy for others to help them.
Can't find that item on mailing list.


https://wiki.postgresql.org/wiki/Guide_to_reporting_problems



Thanks,
Regina







--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Large Binary Columns - Slow Query

[Arjen Nienhuis]

On Jan 10, 2016 21:50, "Saulo Merlo"  wrote:
>
> The binary columns are large so I think that's why the query referencing
them is slow.
>
> PostgreSQL 9.2
>
> Is there a way to speed it up, maybe compression on transfer? Some
indexes has millions of rows...
>
> Maybe create an index to some specifics situations, and not general ones.

Can you show us the table definitions?

What are you trying to do?

>
>
> Thank you.
>
>
> QUERY:
>
> SELECT ni.segment_index AS note_id,
>f.inode_id AS file_id,
>f.node_full_path AS filename,
>f.last_changed AS date_created,
>f.file_data AS main_binary,
>medium.inode_id AS medium_id,
>medium.file_data AS medium_binary,
>thumbnail.inode_id AS thumbnail_id,
>thumbnail.file_data AS thumbnail_binary
> FROM gorfs.nodes AS f
> INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
> INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
> AND mv.segment_index = 'main.with_name'
> INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
> INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
> INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
> LEFT JOIN
>   (SELECT f.inode_id,
>   f.file_data,
>   fi.st_ino
>FROM gorfs.nodes AS f
>INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>AND mv.segment_index = 'medium.with_name'
>INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
) AS medium ON medium.st_ino = fn.st_ino_target
> LEFT JOIN
>   (SELECT f.inode_id,
>   f.file_data,
>   fi.st_ino
>FROM gorfs.nodes AS f
>INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>AND mv.segment_index = 'thumbnail.with_name'
>INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
) AS thumbnail ON thumbnail.st_ino = fn.st_ino_target
> WHERE f.file_data IS NOT NULL LIMIT 500;
>
> EXPLAIN ANALYZE:
>
> "Limit  (cost=20243.45..1593866.18 rows=500 width=180) (actual
time=210.036..44701.406 rows=500 loops=1)"
> "  ->  Nested Loop Left Join  (cost=20243.45..757747912.38 rows=240759
width=180) (actual time=210.036..44701.223 rows=500 loops=1)"
> "->  Nested Loop Left Join  (cost=19724.45..450144827.58
rows=240759 width=148) (actual time=196.881..44353.113 rows=490 loops=1)"
> "  ->  Nested Loop  (cost=19205.46..168833316.21 rows=240759
width=108) (actual time=196.239..43707.952 rows=486 loops=1)"
> "->  Nested Loop  (cost=19205.46..164947484.34
rows=354030 width=107) (actual time=196.233..43706.095 rows=486 loops=1)"
> "  ->  Nested Loop  (cost=19205.46..159233457.64
rows=520593 width=99) (actual time=196.223..43704.141 rows=486 loops=1)"
> "->  Nested Loop
(cost=19205.46..153056028.27 rows=987163 width=107) (actual
time=196.202..43700.535 rows=486 loops=1)"
> "  Join Filter:
((("t"."st_ino")::bigint = ("p"."st_ino_target")::bigint) AND (CASE WHEN
("f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN
(SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL))"
> "  ->  Nested Loop
(cost=19205.46..83831623.03 rows=1458893 width=36) (actual
time=155.629..43307.564 rows=486 loops=1)"
> "Join Filter:
((B''::"bit" & ("t"."st_mode")::"bit") =
("f"."bits")::"bit")"
> "Rows Removed by Join Filter:
10673"
> "->  Nested Loop
(cost=18686.46..83243898.69 rows=1458893 width=41) (actual
time=155.432..43302.768 rows=486 loops=1)"
> "  ->  Nested Loop
(cost=18686.46..59543972.56 rows=2145267 width=41) (actual
time=155.420..43300.521 rows=486 loops=1)"
> "->  Merge Join
(cost=18686.46..29848042.37 rows=4751151 width=16) (actual
time=155.397..43289.955 rows=486 loops=1)"
> "  Merge
Cond: (("fd"."st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
> "  ->  Index
Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd"
(cost=0.00..24356396.96 rows=83232691 width=16) (actual
time=0.080..43016.366 rows=2037019 loops=1)"
> "  ->  Index
Scan using "ix_inode_segments_climb_tree" on "inode_segments" "mv"
(cost=0.00..7695601.08 rows=6986452 width=16) (actual time=0.197..96.693
rows=487 loops=1)"
> "
Index Cond: (("segment_index")::"text" = 'main.with_name'::"text")"
> "   

Re: [GENERAL] Code of Conduct: Is it time?

[Scott Mead]

On Sun, Jan 10, 2016 at 5:09 PM, Adrian Klaver 
wrote:

> On 01/10/2016 02:05 PM, Regina Obe wrote:
>
>> Gavin,
>>
>>> I once went out of my way to help someone with Mac.  They were so Mac
>>>
>> centric they did not realize that they were not giving us the right
>> information to help them, but this was not obvious until later in the
>> thread.  I made some comment about Linux - next moment they were accusing
>>
>>> everyone of not helping them properly because they were using a Mac, as
>>>
>> though we had been deliberately discriminating against them!
>>
>> I hear ya. I do the same thing setting up a Linux VM to try to help
>> someone
>> on Ubuntu or CentOS.
>> My main point was if you don't have anything helpful to say, don't say
>> anything at all.
>>
>> I recall someone posting something earlier about on the lists we should
>> have
>> a section like:
>>
>> HELP US HELP YOU
>>
>> That details the information anyone having a problem should provide to
>> make
>> it easy for others to help them.
>> Can't find that item on mailing list.
>>
>
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems



Maybe I'm out of sync with everyone else, but, I think of list- and IRC
guidelines as distinctly separate from a code of conduct.  I see a code of
conduct as a legal document that allows the community to protect itself
(and individuals its individuals) from illegal and possibly predatory
behavior.  Guidelines for posting: "don't top post, don't paste 500 lines
in to IRC etc... " are things that could get the community to *ignore* you,
but not necessarily cause them to participate in a legal showdown directly
or as a 'third-party'.

   ISTM that if we develop a code of conduct, it would need to be designed
to insulate the community and individuals within it from becoming targets
of legal action.  "Mike said I was bad at postgres, it hurt my consulting
and I want to sue Joe for replying-all and upping the hit-count on
google... "

--Scott

>
>
>>
>> Thanks,
>> Regina
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Code of Conduct: Is it time?

[Regina Obe]

> On 01/10/2016 10:44 AM, Regina Obe wrote:

>>> JD
>>
>> This may come as a big shock to many of you, but as a contributor I 
>> don't care if you are racist, sexist, transphobic or whatever as long 
>> as you

> I think this is reasonable but my point is that we don't care if you are
sexist (in terms of .Org). We care if you allow your sexism to bleed into
the community.

> In short, as long as you are professional and respectful, your personal
beliefs may remain your own.

> JD

Josh,

I read the image and thought, you meant you can't have a racist, sexist
thought in your body and I looked at myself and thought
"I have racist and sexist thoughts. I might be a racist sexist pig.  I am
not welcome here. Let me find another project."

I would remove the word professional.  I think people have used that word so
much to mean newspeak that people are now scared of the term.

So something like:

Try to be helpful and respectful when talking with people in the community.

Thanks,
Regina






-- 
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] Code of Conduct: Is it time?

[Regina Obe]

Gavin,
> I once went out of my way to help someone with Mac.  They were so Mac
centric they did not realize that they were not giving us the right
information to help them, but this was not obvious until later in the
thread.  I made some comment about Linux - next moment they were accusing 
> everyone of not helping them properly because they were using a Mac, as
though we had been deliberately discriminating against them!

I hear ya. I do the same thing setting up a Linux VM to try to help someone
on Ubuntu or CentOS.
My main point was if you don't have anything helpful to say, don't say
anything at all.

I recall someone posting something earlier about on the lists we should have
a section like:

HELP US HELP YOU

That details the information anyone having a problem should provide to make
it easy for others to help them.
Can't find that item on mailing list.


Thanks,
Regina




-- 
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] Code of Conduct: Is it time?

[Jim Nasby]

On 1/10/16 10:07 AM, Bill Moran wrote:

The fact that Postgres has not needed a CoC up till now is a
testiment to the quality of the people in the community. However,
if Postgres continues to be more popular, the number of people
involved is going to increase. Simply as a factor of statistics,
the project will be forced to deal with some unsavory people at
some point. Having a CoC is laying the foundation to ensure that
dealing with those people involves the least pain possible. It
will always involve_some_  pain, but less is better.

I've done the job of #3 with other groups, and 99% of the time
there was nothing to do. The one incident I had to handle was
terrible, but at least I had some guidance on how to deal with
it.


Bingo.

To me, the CoC is as much about protecting Postgres itself as it is 
about protecting contributors. Haters are going to hate, no matter what 
you do... so how do you remove them and their toxicity as cleanly as 
possible?


BTW, IMHO I think it was a mistake for the FreeBSD community to try and 
keep things quiet. Sweeping stuff like this under the rug doesn't help 
anyone. The problem is how to publicize things without scaring people 
away from reporting. Also, not allowing your CoC to become a weapon that 
someone can use offensively.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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