Re: [GENERAL] A unique pairs version of UNNEST() ?

[Alban Hertroys]

> On 04 Jan 2016, at 21:08, Wells Oliver  wrote:
> 
> Hey all, happy new year.
> 
> I am trying to get unique pairs from an array of N numbered items, usually 5, 
> but possibly 4 or 6.
> 
> If I just wanted unique values, I could do SELECT UNNEST(list_of_ids) AS id, 
> COUNT(*) FROM table GROUP BY id but in this situation I want all unique pairs 
> and a COUNT.
> 
> For those familiar with python, this is the functionality found in 
> itertools.combinations. I'm leaning towards just doing this in python, but I 
> really like keeping as much in SQL as possible.
> 
> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
> 
> {1, 2}
> {1, 3}
> {1, 4}
> {1, 5}
> {2, 3}
> {2, 4}
> {2, 5}
> {3, 4}
> {3, 5}
> {4, 5}

Your example reminds me of combinatory theory. The result you seem to be 
looking for is the list of possible unique combinations, as sets of elements of 
the total set (sets are orderless).

with list_of_ids as (
  select unnest(list_of_ids) as id from table
)
select a.id, b.id
  from list_of_ids a, list_of_ids b
 where b.id > a.id;

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
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] Streaming replication stacked.

[Yoji]

Hi, Andreas 

Thank you for replying.

You're right, I have only 1 slave.

And I need running transactions on slave.
Once I restarted postgres service on slave and then process began to move.

Best regards.

Yoji



--
View this message in context: 
http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880326.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Streaming replication stacked.

[Yoji]

Hi Michael,

Thank you for replying.

I'm using version of 9.4.3.

And I have seen same behavior on  version of 9.3.4.

Best regards.


Yoji 



--
View this message in context: 
http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880329.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

Yoji  wrote:

> Hi, Andreas 
> 
> Thank you for replying.
> 
> You're right, I have only 1 slave.
> 
> And I need running transactions on slave.
> Once I restarted postgres service on slave and then process began to move.

ok, i think it's clear now: because of running transactions on the
standby (and hot_standby_feedback on) the master has to wait for the
slave and can't replay all from the master.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

Re: [GENERAL] Streaming replication stacked.

[Andreas Kretschmer]

Andreas Kretschmer  wrote:

> ok, i think it's clear now: because of running transactions on the
> standby (and hot_standby_feedback on) the master has to wait for the
> slave and can't replay all from the master.

Mhh. Maybe i'm wrong, can't reproduce that.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

Re: [GENERAL] grep -f keyword data query

[Hiroyuki Sato]

Hello Arjen.

Thank you for replying.

I'll try OR query on my environment.
Thanks.



2016年1月4日(月) 23:03 Arjen Nienhuis :

>
> On Jan 4, 2016 09:45, "Hiroyuki Sato"  wrote:
> >
> > Hello Arjen
> >
> > Thank you for replying.
> >
> > 2016年1月4日(月) 16:49 Arjen Nienhuis :
> >>
> >>
> >> On Dec 28, 2015 00:55, "Hiroyuki Sato"  wrote:
> >> >
> >> > Hello Andreas and Tom
> >> >
> >> > Thank you for replying.
> >> >
> >> > Sorry, I re-created my questions. I was mis-pasted query log on
> previous question.
> >> > (@~ operator is PGroonga extension (http://pgroonga.github.io))
> >> > Please ignore it.
> >> >
> >> > Best regards.
> >> >
> >> > 1, Problem.
> >> >   (1) Following query is exteme slow. (478sec)
> >> > SELECT
> >> >   u.url
> >> > FROM
> >> >   url_lists4 u,
> >> >   keywords4 k
> >> > WHERE
> >> >   u.url like k.url
> >> > AND
> >> >   k.name = 'esc_url';
> >> >
> >> >
> >> >   (2) grep -f kwd.txt sample.txt (exec time under 1sec)
> >> >
> >>
> >> These are not the same 'query'. Grep will match any of the patterns for
> each url while postgres finds all matching combinations. You need to use
> '... OR ... OR ...' or 'ANY()' in postgres to get the same result.
> >
> > I thought the following query are same meaning.
> > Could you point me same examples about two differences?
>
> If one URL matches two patterns, grep will return the match only once. In
> your SQL query it will match multiple times and will be in the result
> multiple times.
>
> You can test this by putting both 'http' and 'yahoo' in the wordlist.
>
> >
> >   (1) u.url like k.url
> >   (2) u.url like 'k.url 1', or u.url like 'k.url2' ...
> >
> >> > 2, Questions
> >> >
> >> >   (1) Is it possible to improve this query like the command ``grep -f
> keyword data``?
> >>
> >> I get the best results by using OR of all the different patterns:
> >>
> >> SELECT url FROM url_lists4
> >> WHERE
> >> url LIKE 'http://ak.yahoo.co.jp/xwv/%'
> >> OR url LIKE 'http://ao.yahoo.co.jp/wdl/%'
> >> OR ...
> >
> >
> > I'll try it.
> >
> > BTW Do you know how many OR can I use ?
> > I have 5000 URLs.
>
> There is no real limit. I tried with your test file (5000 patterns). I got
> 6 seconds planning time and 12 seconds execution time.
>
> (I also tried making one big regular expression with url1|url2|... but
> that did fail)
>
> >
> >> In theory you could use:
> >>
> >> ... WHERE url LIKE ANY(ARRAY(SELECT url FROM keywords4 k WHERE k.name
> = 'esc_url'));
> >>
> >> but that's very slow.
> >
> > It's very interesting. It seems same query.
> > I'll check EXPLAIN
> >
> >
> >>
> >> >   (2) What kind of Index should I create on url_lists table?
> >>
> >> Both btree text_pattern_ops and gin trigram on the URL.
> >>
> >> >
> >> > 3, Environment
> >> >   OS: CentOS7
> >> >   PostgreSQL 9.4
> >> >
> >> > 4, sample source
> >> >   https://github.com/hiroyuki-sato/postgres_like_test
> >> >
> >> >
> >> > 5, Create table
> >> >
> >> > drop table if exists url_lists4;
> >> > create table url_lists4 (
> >> >   id int not null primary key,
> >> >   url text not null
> >> > );
> >> > create index ix_url_url_lists4 on url_lists4(url);
> >> >
> >> > drop table if exists keywords4;
> >> > create table keywords4 (
> >> >   id int not null primary key,
> >> >   name varchar(40) not null,
> >> >   url text not null
> >> > );
> >> >
> >> > create index ix_url_keywords4 on keywords4(url);
> >> > create index ix_name_keywords4 on keywords4(name);
> >> >
> >> >
> >> > \copy url_lists4(id,url) from 'sample.txt' with delimiter ',';
> >> > \copy keywords4(id,name,url) from 'keyword.txt' with delimiter
> ',';
> >> >
> >> > vacuum url_lists4;
> >> > vacuum keywords4;
> >> > analyze url_lists4;
> >> > analyze keywords4;
> >> >
> >> > 6, Query
> >> >
> >> > EXPLAIN SELECT
> >> >   u.url
> >> > FROM
> >> >   url_lists4 u,
> >> >   keywords4 k
> >> > WHERE
> >> >   u.url like k.url
> >> > AND
> >> >   k.name = 'esc_url';
> >> >
> >> > EXPLAIN ANALYZE SELECT
> >> >   u.url
> >> > FROM
> >> >   url_lists4 u,
> >> >   keywords4 k
> >> > WHERE
> >> >   u.url like k.url
> >> > AND
> >> >   k.name = 'esc_url';
> >> >
> >> >
> >> > SELECT
> >> >   u.url
> >> > FROM
> >> >   url_lists4 u,
> >> >   keywords4 k
> >> > WHERE
> >> >   u.url like k.url
> >> > AND
> >> >   k.name = 'esc_url';
> >> >
> >> > 7, EXPLAIN
> >> >
> >> >  QUERY PLAN
>
> >> >
> -
> >> >  Nested Loop  (cost=0.00..37510799.00 rows=1250 width=57)
> >> >Join Filter: (u.url ~~ k.url)
> >> >->  Seq Scan on url_lists4 u  (cost=0.00..10682.00 rows=50
> width=57)
> >> >->  

Re: [GENERAL] SSL connection issue via perl

[George Woodring]

Just to add a final resolution to this thread, my solution was to add "use
Net::SSL" to the top of my script.  Even though my script does not use SSL
directly, I think this must have helped in loading the libraries in an
order that let everything work.

Thanks for the help

iGLASS Networks
www.iglass.net

On Thu, Dec 31, 2015 at 2:29 PM, George Woodring  wrote:

> OS: CentOS 6.6
> Postgres Version: 9.3.10
>
> I have a script that is worked for years that does the following
>
> - Connect to postgres and get a list of URLs to poll for status
> - close connection
> - Start threads to poll the URLs
> - cleanup threads and collect the results.
> - Connect to postgres and write the url status.
> - close connection
>
> We updated perl SSL libraries to the latest version, one of which was
> Net::SSLeay 1.35 -> 1.72
>
> Now the script dies without any feedback when attempting the 2nd
> connection.  The only hint at the problem is
>
> /var/log/messages
> Dec 31 14:04:03 iprobe002 kernel: iPoller2.pl[16044] general protection
> ip:7f677fde112c sp:7fff5db9e328 error:0 in SSLeay.so[7f677fd6a000+94000]
>
> /var/log/postgresql
> Dec 31 14:04:03 iprobe002 postgres[16255]: [4-1] LOG:  could not accept
> SSL connection: EOF detected
>
> I have worked around the immediate issue by keeping the 1st connection
> open for the entire script instead of making 2 connections, but I would
> like to try to find out what is going wrong.
>
> Any suggestions would be appreciated.
> Thanks,
> George
>
>
> iGLASS Networks
> www.iglass.net
>

Re: [GENERAL] select and count efficiency (~35 mln rows)

[Grzegorz Kuczera]

Please read the links below:

http://www.depesz.com/2007/08/29/better-results-paging-in-postgresql-82/
https://wiki.postgresql.org/wiki/File:Pagination_Done_the_PostgreSQL_Way.pdf
https://coderwall.com/p/lkcaag


I am reading the articles right now.


>There are about 35 milion records in the table.

do you really want to show all records, even with pagination? REALLY?



There are different accounts and rights in the system, most offen the 
number of rows is narrowed down. But there is one account type, which 
can see that many results.
And to be precise - problem appears even for the users, who are not able 
to access the hole table.





smime.p7s
Description: Kryptograficzna sygnatura S/MIME

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

[Adrian Klaver]

On 01/05/2016 08:47 AM, Joshua D. Drake wrote:

Hello,

I had a hard time writing this email. I think Code of Conducts are
non-essential, a waste of respectful people's time and frankly if you
are going to be a jerk, our community will call you out on it.
Unfortunately a lot of people don't agree with that. I have over the
course of the last year seen more and more potential users very
explicitly say, "I will not contribute to a project or attend a
conference that does not have a CoC".


The Brendan Eich fiasco at Mozilla taught me all I need to know about 
CoC's and their uselessness and un-enforceability.




Some of us may be saying, "Well we don't want those people". I can't
argue with some facts though. Ubuntu has had a CoC[1] since the
beginning of the project and they grew exceedingly quick. Having walls
in the hallway of interaction isn't always a bad thing.

In reflection, the only thing a CoC does is put in writing what
behaviour we as a project already require, so why not document it and
use it as a tool to encourage more contribution to our project?

Sincerely,

JD


1. http://www.ubuntu.com/about/about-ubuntu/conduct



--
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?

[Joshua D. Drake]

On 01/05/2016 08:56 AM, Melvin Davidson wrote:

Joshua,

I have to agree that a COC is in order. But to add to that, I would like
to see basic requirements when submitting a problem.
IE: 1. Version of PostgreSQL
  2.. O/S
  3. Enough info to duplicate the problem EG: minimal schema & data


Although I agree with you. This is completely off topic for this thread. 
Please create a new thread for those requests. I don't want to see this 
thread hijacked.


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?

[Joshua D. Drake]

On 01/05/2016 09:06 AM, Adrian Klaver wrote:

Brendan Eich fiasco


Has absolutely nothing to do with a CoC. At least from my understanding 
of what happened.


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] [SQL] plv8 installation problem

[Adrian Klaver]

On 01/05/2016 09:02 AM, Belju Paul wrote:

Ccin list

Both Postgre 9.4 and plv8 are downloaded from postgre site


So does plv8.dll exist?



Belju Paul

On Jan 5, 2016 9:30 PM, "Adrian Klaver" > wrote:

On 01/05/2016 12:56 AM, Belju Paul wrote:

Hi,

"PostgreSQL 9.4 plv8 32-bit download - PL/V8 1.4.2ish" not
working in my
Windows XP 32-Bit OS with PostgreSQL 9.4. While running "CREATE
EXTENSION plv8" shows the error could not load library plv8.dll.
Help me
to work plv8 in WinXP platform.


Well first XP is a no longer supported OS so it is possible this is
not fixable. Still it is worth a shot. Before we can start though we
will need some more information:

1) How did you install Postgres 9.4 to begin with?

2) Where did you get the plv8 download and how did you install it?

3) Does plv8.dll exist on the system?


Thanks & Regards
Belju Paul
9447732043



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




--
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?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> In reflection, the only thing a CoC does is put in writing what 
> behaviour we as a project already require, so why not document it and 
> use it as a tool to encourage more contribution to our project?

+1, been thinking about this same thing recently. I disagree that 
it is a waste of time, but I'm happy if we get one, regardless 
of different people's rationales for it.

- -- 
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201601051213
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlaL+esACgkQvJuQZxSWSsgjXgCdGpieS5ys6jetbFk21XWIsdip
PzQAnRiBOiH/5EtaVyag3xihpFddk40W
=Urw3
-END PGP SIGNATURE-




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

[GENERAL] Code of Conduct: Is it time?

[Joshua D. Drake]

Hello,

I had a hard time writing this email. I think Code of Conducts are 
non-essential, a waste of respectful people's time and frankly if you 
are going to be a jerk, our community will call you out on it. 
Unfortunately a lot of people don't agree with that. I have over the 
course of the last year seen more and more potential users very 
explicitly say, "I will not contribute to a project or attend a 
conference that does not have a CoC".


Some of us may be saying, "Well we don't want those people". I can't 
argue with some facts though. Ubuntu has had a CoC[1] since the 
beginning of the project and they grew exceedingly quick. Having walls 
in the hallway of interaction isn't always a bad thing.


In reflection, the only thing a CoC does is put in writing what 
behaviour we as a project already require, so why not document it and 
use it as a tool to encourage more contribution to our project?


Sincerely,

JD


1. http://www.ubuntu.com/about/about-ubuntu/conduct
--
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?

[Melvin Davidson]

Joshua,

I have to agree that a COC is in order. But to add to that, I would like to
see basic requirements when submitting a problem.
IE: 1. Version of PostgreSQL
 2.. O/S
 3. Enough info to duplicate the problem EG: minimal schema & data

On Tue, Jan 5, 2016 at 11:47 AM, Joshua D. Drake 
wrote:

> Hello,
>
> I had a hard time writing this email. I think Code of Conducts are
> non-essential, a waste of respectful people's time and frankly if you are
> going to be a jerk, our community will call you out on it. Unfortunately a
> lot of people don't agree with that. I have over the course of the last
> year seen more and more potential users very explicitly say, "I will not
> contribute to a project or attend a conference that does not have a CoC".
>
> Some of us may be saying, "Well we don't want those people". I can't argue
> with some facts though. Ubuntu has had a CoC[1] since the beginning of the
> project and they grew exceedingly quick. Having walls in the hallway of
> interaction isn't always a bad thing.
>
> In reflection, the only thing a CoC does is put in writing what behaviour
> we as a project already require, so why not document it and use it as a
> tool to encourage more contribution to our project?
>
> Sincerely,
>
> JD
>
>
> 1. http://www.ubuntu.com/about/about-ubuntu/conduct
> --
> 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
>



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

[GENERAL] Unique constraints and indexes.

[Steve Rogerson]

Is this a bug? I create a "unique" index, directly but it doesn't add a unique
constraint. Add a unique constraint and it adds the index and the constraint.
(pg version 9.4.5 on fedora 22, but also occurs in other versions).
Functionally I can't see a difference.

mydb=# create table test_table ( f1 bigint, f2 bigint);
CREATE TABLE
mydb=# create unique index test_table_un on test_table (f1, f2);
CREATE INDEX
mydb=# \d test_table
  Table "public.test_table"
 Column |  Type  | Modifiers
++---
 f1 | bigint |
 f2 | bigint |
Indexes:
"test_table_un" UNIQUE, btree (f1, f2)

mydb=# select conindid, contype, conname from pg_constraint where conname like
'test_table%';
 conindid | contype | conname
--+-+-
(0 rows)



-- --
mydb=# drop table test_table;
DROP TABLE
mydb=# create table test_table ( f1 bigint, f2 bigint);
CREATE TABLE
mydb=# alter table test_table add constraint test_table_un unique (f1,f2);
ALTER TABLE
mydb=# \d test_table
  Table "public.test_table"
 Column |  Type  | Modifiers
++---
 f1 | bigint |
 f2 | bigint |
Indexes:
"test_table_un" UNIQUE CONSTRAINT, btree (f1, f2)

mydb=# select conindid, contype, conname from pg_constraint where conname like
'test_table%';
 conindid | contype |conname
--+-+---
  4284073 | u   | test_table_un
(1 row)

mydb=#


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

[GENERAL] BDR install broken on Ubuntu 14.04

[Roland van Laar]

Hi,

I'm installing BDR on a ubuntu 14.04 (Trusty) machine. I can reproduce 
this error.

It happens locally with a new VM and on Digital ocean.
The error is:

/var/lib/dpkg/info/postgresql-bdr-9.4.postinst: 95: 
/var/lib/dpkg/info/postgresql-bdr-9.4.postinst: db_stop: not found


How can I fix this?

The full installation log is included below.

Regards,

Roland van Laar

sudo apt-get update
sudo apt-get upgrade
wget -qO - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc |sudo 
apt-key add -
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc |sudo 
apt-key add -
sudo add-apt-repository 'deb http://packages.2ndquadrant.com/bdr/apt/ 
trusty-2ndquadrant main'
sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ 
trusty-pgdg main'

sudo apt-get update
sudo apt-get install postgresql-bdr-9.4-bdr-plugin


The full installation log:
$ sudo apt-get install postgresql-bdr-9.4-bdr-plugin
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer 
required:

  linux-headers-3.13.0-57 linux-headers-3.13.0-57-generic
  linux-headers-3.13.0-61 linux-headers-3.13.0-61-generic
  linux-image-3.13.0-57-generic linux-image-3.13.0-61-generic
  linux-image-extra-3.13.0-57-generic linux-image-extra-3.13.0-61-generic
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libpq5 libxslt1.1 pgdg-keyring postgresql-bdr-9.4 
postgresql-bdr-client-9.4

  postgresql-bdr-contrib-9.4 postgresql-client-common postgresql-common
  ssl-cert
Suggested packages:
  oidentd ident-server locales-all postgresql-bdr-doc-9.4 libdbd-pg-perl
  openssl-blacklist
The following NEW packages will be installed:
  libpq5 libxslt1.1 pgdg-keyring postgresql-bdr-9.4
  postgresql-bdr-9.4-bdr-plugin postgresql-bdr-client-9.4
  postgresql-bdr-contrib-9.4 postgresql-client-common postgresql-common
  ssl-cert
0 upgraded, 10 newly installed, 0 to remove and 3 not upgraded.
Need to get 5,995 kB of archives.
After this operation, 28.4 MB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://mirrors.digitalocean.com/ubuntu/ trusty/main libxslt1.1 
amd64 1.1.28-2build1 [145 kB]
Get:2 http://mirrors.digitalocean.com/ubuntu/ trusty/main ssl-cert all 
1.0.33 [16.6 kB]
Get:3 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-client-9.4 amd64 9.4.5-1trusty [1,068 kB]
Get:4 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-9.4 amd64 9.4.5-1trusty [3,677 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main libpq5 
amd64 9.4.5-1.pgdg14.04+1 [122 kB]
Get:6 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-contrib-9.4 amd64 9.4.5-1trusty [443 kB]
Get:7 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-9.4-bdr-plugin amd64 0.9.3-1trusty [230 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
pgdg-keyring all 2014.1 [5,898 B]
Get:9 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
postgresql-client-common all 171.pgdg14.04+1 [76.6 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
postgresql-common all 171.pgdg14.04+1 [210 kB]

Fetched 5,995 kB in 1s (4,540 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libpq5:amd64.
(Reading database ... 146095 files and directories currently installed.)
Preparing to unpack .../libpq5_9.4.5-1.pgdg14.04+1_amd64.deb ...
Unpacking libpq5:amd64 (9.4.5-1.pgdg14.04+1) ...
Selecting previously unselected package libxslt1.1:amd64.
Preparing to unpack .../libxslt1.1_1.1.28-2build1_amd64.deb ...
Unpacking libxslt1.1:amd64 (1.1.28-2build1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../pgdg-keyring_2014.1_all.deb ...
Unpacking pgdg-keyring (2014.1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../postgresql-client-common_171.pgdg14.04+1_all.deb ...
Unpacking postgresql-client-common (171.pgdg14.04+1) ...
Selecting previously unselected package postgresql-bdr-client-9.4.
Preparing to unpack 
.../postgresql-bdr-client-9.4_9.4.5-1trusty_amd64.deb ...

Unpacking postgresql-bdr-client-9.4 (9.4.5-1trusty) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../ssl-cert_1.0.33_all.deb ...
Unpacking ssl-cert (1.0.33) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../postgresql-common_171.pgdg14.04+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev 
by postgresql-common'

Unpacking postgresql-common (171.pgdg14.04+1) ...
Selecting previously unselected package postgresql-bdr-9.4.
Preparing to unpack .../postgresql-bdr-9.4_9.4.5-1trusty_amd64.deb ...
Unpacking postgresql-bdr-9.4 (9.4.5-1trusty) ...
Selecting previously unselected package postgresql-bdr-contrib-9.4.
Preparing to unpack 

Re: [GENERAL] BDR and TX obeyance

[Edson Richter]

Em 05/01/2016 11:42, Riley Berton escreveu:

Edson Richter  writes:


BTW, I'm also looking for a "synchronous multi-master" solution... If
you find one, please share :-)
The only solution I've found so far is a middleware that is close, the
C-Jdbc/Sequoia, which seems not being actively maintained for a while
now.

See Postgres-R for sync multi-master.
http://www.postgres-r.org/documentation/

Note that it is specifically geared towards low-latency environments and
is likely not suitable for geo-distributed applications. It hasn't been
touched in 4 years so likely not actively maintained.

riley


That seems to be what I'm looking for...
As soon as I get some free time, I'll give a try.

Regards,

Edson




--
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] BDR and TX obeyance

[Riley Berton]

Edson Richter  writes:

> BTW, I'm also looking for a "synchronous multi-master" solution... If 
> you find one, please share :-)
> The only solution I've found so far is a middleware that is close, the 
> C-Jdbc/Sequoia, which seems not being actively maintained for a while
> now.

See Postgres-R for sync multi-master.
http://www.postgres-r.org/documentation/

Note that it is specifically geared towards low-latency environments and
is likely not suitable for geo-distributed applications. It hasn't been
touched in 4 years so likely not actively maintained.

riley

>
> Regards,
>
> Edson
>
> Atenciosamente,
>
> Edson Carlos Ericksson Richter
>
> Em 04/01/2016 18:09, Riley Berton escreveu:
>> I have been experimenting with BDR and have a question about how BDR
>> interacts with transactions.
>>
>> bdrdemo=# create table thingy (id INT, value TEXT, PRIMARY KEY(id));
>> CREATE TABLE
>> bdrdemo=# create table tx_log(id INT, msg TEXT, PRIMARY KEY(id));
>> CREATE TABLE
>> bdrdemo=# insert into thingy (id, value) VALUES (1, 'insert from node1');
>> INSERT 0 1
>>
>>  From node1:
>>
>> bdrdemo=# begin;
>> BEGIN
>> bdrdemo=# update thingy set value='update from node1' where id=1;
>> UPDATE 1
>> bdrdemo=# insert into tx_log (id, msg) values (1, 'tx log insert from 
>> node1');
>> INSERT 0 1
>> bdrdemo=# commit;
>> COMMIT
>>
>> Simultaneously from node2:
>>
>> bdrdemo=# begin;
>> BEGIN
>> bdrdemo=# update thingy set value='update from node2' where id=1;
>> UPDATE 1
>> bdrdemo=# insert into tx_log (id, msg) values (2, 'tx log insert from 
>> node2');
>> INSERT 0 1
>> bdrdemo=# commit;
>> COMMIT
>>
>> ...
>>
>> bdrdemo=# select * from tx_log ;
>>   id |   msg
>> +--
>>1 | tx log insert from node1
>>2 | tx log insert from node2
>> (2 rows)
>>
>> bdrdemo=# select * from thingy ;
>>   id |   value
>> +---
>>1 | update from node2
>> (1 row)
>>
>> The conflict on the "thingy" table has resulted in node2 winning based
>> on last_update wins default resolution.  However, both inserts have
>> applied.  My expectation is that the entire TX applies or does not
>> apply.  This expectation is clearly wrong.
>>
>> Question is: is there a way (via a custom conflict handler) to have the
>> TX obeyed?  I can't see a way to even implement a simple bank account
>> database that changes multiple tables in a single transaction without
>> having the data end up in an inconsistent state.  Am I missing something
>> obvious here?
>>
>> Thanks in advance for any help.
>>
>> riley
>>
>
>
>
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

[GENERAL] Getting the function definition from oid in before firing the function

[Mohammed Ajil]

Dear Postgres Team,

For my thesis about secure access control for database systems I need to
implement a new decision algorithm for deciding if commands can be
executed. For that algorithm to work I need to keep a stack that holds
all the commands that are currently running, like a stacktrace.

Each stack entry should contain the following:
- Session user
- User that runs the command (differs if we have security definer on
triggers)
- Command
- Oid of trigger (if it is one)

Now my problem consists of two steps:
- First I need to decide if the algorithm supports the trigger type
(only of LANGUAGE PLPGSQL).
- Second I need to get the SQL command that the function will execute.

What I tried was the following:
In the file trigger.c I have found the location where the function is
called. There I have access to the FunctionCallInfoData struct.
I have seen on
http://www.postgresql.org/docs/9.4/static/functions-info.html that there
is a function which should return the functions definition as a Datum
from the function oid. Now this is a bit weird since in the source code
the function expects a pointer to a FunctionCallInfoData struct, which
is no problem since I have access to both there.
But when I pass the the pointer to the FunctionCallInfoData I always get
an error stating that the cache lookup failed. I really don't know why,
since when I investigate the struct fcinfo in gdb it has valid entries
and also the oid of the function is accessible.

I would be glad for some help here.

Also another problem that I have at the moment is the following:
My advisor wanted to have a uniform interface for access control, thus I
have refactored the code of postgres and moved the relevant access
control parts to a new module (src/backend/access_control). Now I have
put the header files in src/include/access_control. What I don't quite
get is how to get make a Makefile that delivers the object files as
postgres needs them to link them to the main binary. Is it enough to
make a Makefile that just creates the object files and leaves them in
the correct folder for the root Makefile to pick them up or do I need to
modify the root Makefile too?

Thank you very much for your help in advance, for both problems I have
tried many variations and researched quite much, but did not find a
solution.

Kind regards,

Mohammed Ajil

-- 

Mohammed Ajil
Bsc CS D-INFK
aj...@student.ethz.ch


-- 
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] Please help! Server process was terminated by signal 11: Segmentation fault

[Adrian Klaver]

On 01/04/2016 06:07 PM, John Hong wrote:

Hello Everyone,

On 2015/12/30 17:51:01, Transaction was failed on our system.

I checked the pg_log, it states as below:

2015-12-30 17:51:01 CST [11260]: [42-1] LOG:  0: server process (PID
14272) was terminated by signal 11: Segmentation fault






On my case, Please give me the instruction in steps on how to figure out
the

reason why did this “signal 11” happen and how to solve “signal 11:
Segmentation fault” issue.


The part that confuses me is:

"
2015-12-30 17:50:52 CST [18954]: [5195-1] LOG:  0: duration: 0.111 ms

2015-12-30 17:50:52 CST [18954]: [5196-1] LOCATION:  exec_simple_query, 
postgres.c:1364


★2015-12-30 17:51:02 CST [14276]: [1-1] LOG:  0: database system was 
interrupted; last known up at 2015-12-30 17:50:21 CST★


★2015-12-30 17:51:02 CST [14276]: [31-1] LOG:  0: last completed 
transaction was at log time 2015-12-30 17:50:52.793516+08★


2015-12-30 17:51:01 CST [14270]: [1-1] LOG:  0: connection received: 
host=[local]


"

Is the above a cut and paste error or did the server really go backwards 
in time?


At any rate this:
"
2015-12-30 17:51:01 CST [14270]: [9-1] LOG:  0: statement: select 
current_timestamp,pid,current_timestamp - query_start as 
runtime,datname,usename,client_addr,query


2015-12-30 17:51:01 CST [14270]: [10-1] LOCATION:  exec_simple_query, 
postgres.c:1082


2015-12-30 17:51:01 CST [14272]: [3-1] LOG:  0: connection 
authorized: user=enterprisedb database=edb


2015-12-30 17:51:01 CST [14272]: [4-1] LOCATION:  PerformAuthentication, 
postinit.c:265


2015-12-30 17:51:01 CST [14271]: [5-1] LOG:  0: statement: select 
CURRENT_TIMESTAMP(0), client_addr, count(client_addr) from 
pg_stat_activity group by client_addr order by client_addr;


2015-12-30 17:51:01 CST [14271]: [6-1] LOCATION:  exec_simple_query, 
postgres.c:1082


2015-12-30 17:51:01 CST [14272]: [5-1] LOG:  0: statement: select now();

2015-12-30 17:51:01 CST [14272]: [6-1] LOCATION:  exec_simple_query, 
postgres.c:1082


2015-12-30 17:51:01 CST [14272]: [7-1] LOG:  0: duration: 0.997 ms

2015-12-30 17:51:01 CST [14272]: [8-1] LOCATION:  exec_simple_query, 
postgres.c:1364


2015-12-30 17:51:01 CST [14272]: [9-1] LOG:  0: statement: SELECT 
bl.pid AS blocked_pid,


"

which includes the statement that was at fault:

"★2015-12-30 17:51:01 CST [11260]: [42-1] LOG:  0: server process 
(PID 14272) was terminated by signal 11: Segmentation fault★


★2015-12-30 17:51:01 CST [11260]: [43-1] DETAIL:  Failed process was 
running: SELECT bl.pid AS blocked_pid,★

"

looks like something coming from a monitoring/logging process, if that 
helps.




Thank you in advance.

John







--
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?

[Roland van Laar]

On January 5, 2016 5:47:16 PM GMT+01:00, "Joshua D. Drake" 
 wrote:
>Hello,
>
>I had a hard time writing this email. I think Code of Conducts are 
>non-essential, a waste of respectful people's time and frankly if you 
>are going to be a jerk, our community will call you out on it. 
>Unfortunately a lot of people don't agree with that. I have over the 
>course of the last year seen more and more potential users very 
>explicitly say, "I will not contribute to a project or attend a 
>conference that does not have a CoC".

Do they give a rational for that?
>
>Some of us may be saying, "Well we don't want those people". I can't 
>argue with some facts though. Ubuntu has had a CoC[1] since the 
>beginning of the project and they grew exceedingly quick. Having walls 
>in the hallway of interaction isn't always a bad thing.
>
>In reflection, the only thing a CoC does is put in writing what 
>behaviour we as a project already require, so why not document it and 
>use it as a tool to encourage more contribution to our project?
>
>Sincerely,
>
>JD
>
>
>1. http://www.ubuntu.com/about/about-ubuntu/conduct



-- 
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] Unique constraints and indexes.

[Tom Lane]

Steve Rogerson  writes:
> On 05/01/16 19:47, Tom Lane wrote:
>> That's operating as designed.  A unique constraint needs an index,
>> but not vice versa.

> I can see that might be plausible , hence the question but as a "unique index"
> imposes as constraint they seem equivalent. What's the functional difference
> between the two situations?

There is none so far as uniqueness-enforcement is concerned, because the
index is the same either way, and that's what enforces it.

The main reason we don't automatically create a constraint for every
unique index is that not all index declarations can be represented
by SQL-standard constraints.

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] Unique constraints and indexes.

[Steve Rogerson]

On 05/01/16 19:47, Tom Lane wrote:
> Steve Rogerson  writes:
>> Is this a bug? I create a "unique" index, directly but it doesn't add a 
>> unique
>> constraint. Add a unique constraint and it adds the index and the constraint.
> 
> That's operating as designed.  A unique constraint needs an index,
> but not vice versa.


I can see that might be plausible , hence the question but as a "unique index"
imposes as constraint they seem equivalent. What's the functional difference
between the two situations?

Steve




-- 
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?

[Bret Stern]

On Tue, 2016-01-05 at 22:41 -0600, Jim Nasby wrote:

> On 1/5/16 10:03 PM, John R Pierce wrote:
> > On 1/5/2016 5:31 PM, Jim Nasby wrote:
> >> IMHO, the real problem here is not simply a CoC, it is that the
> >> Postgres community doesn't focus on developing the community itself.
> >> The closest we come to "focus" is occasional talk on -hackers about
> >> how we need more developers. There is no formal
> >> discussion/leadership/coordination towards actively building and
> >> strengthening our community. Until that changes, I fear we will always
> >> have a lack of developers. More importantly, we will continue to lack
> >> all the other ways that people could contribute beyond writing code.
> >> IE: the talk shouldn't be about needing more developers, it should be
> >> about needing people who want to contribute time to growing the
> >> community.
> >
> >
> > That sounds like a bunch of modern marketing graduate mumbojumbo to
> > me.The postgres community are the people who actually support it on
> > the email lists and IRC, as well as the core development teams, and
> > INMO, they are quite strong and effective. when you start talking
> > about social marketing and facebook and twitter and stuff, thats just a
> > bunch of feelgood smoke and mirrors.The project's output is what
> > supports it, not having people going out 'growing community', that is
> > just a bunch of hot air.   you actively 'grow community' when you're
> > pushing worthless products (soda pop, etc) based on slick marketing
> > plans rather than actually selling something useful.
> 
> Then why is it that there is almost no contribution to the community 
> other than code and mailing list discussion?
> 
> Why is the infrastructure team composed entirely of highly experienced 
> code contributors, of which there are ~200 on the planet, when there are 
> literally 100s of thousands (if not millions) of people out there that 
> could do that work (and could probably do it better if it's what they do 
> for a living, no offense to the efforts of the infrastructure team).
> 
> Why is there a lack of developers? And a serious lack of code reviewers?
> -- 
> 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
> 

As long as I've participated in the list, I've had access to the very
best conversations
and technical discussions from my fellow decorated contributors. 

The coc sounds like a Washington politics play, but as long as the best
still engage
in this forum, I could care less. The list serves its purpose without
overhead...a rare
resource in today's flood of incoherent technical chatter.

Happy New Year!
Bret Stern
President
Machine Management

Re: [GENERAL] Efficiently selecting single row from a select with window functions row_number, lag and lead

[David Rowley]

On 2 January 2016 at 16:39, Andrew Bailey  wrote:

> I would like to do the following:
>
> select id, row_number() over w as rownum, lag(id, 1) over w as prev,
> lead(id, 1) over w as next from route where id=1350 window w as (order by
> shortname, id asc rows between 1 preceding and 1 following)  order by
> shortname, id ;
>
> However this gives the result
> 1350;1;;
>
>
This does not work due to the id=1350 is always applied before the rows
make it into the window therefore you only have rows which match id=1350,
which is not what you want in this case.


> The following query gives the result I am expecting
>
> select * from (select id, row_number() over w as rownum,
> lag(id, 1) over w as prev, lead(id, 1) over w as next
> from route window w as (order by shortname, id
> rows between 1 preceding and 1 following) order by shortname, id) as s
> where id=1350
>
> 1350;3;1815;1813
>
>
This works because the id=1350 is not pushed down into the subquery which
contain the windowing functions, this also means that the entire route
table is processed and you may suffer from performance problems if the
route table is, or gets big. You'll be able to confirm this by looking at
the EXPLAIN output and noticing the lack of filter on the seqscan.


> The explain plan is
> "Subquery Scan on s  (cost=0.14..15.29 rows=1 width=32)"
> "  Filter: (s.id = 1350)"
> "  ->  WindowAgg  (cost=0.14..13.51 rows=143 width=12)"
> "->  Index Only Scan using route_idx on route  (cost=0.14..10.29
> rows=143 width=12)"
>
> as it makes use of the index created as follows
>
> CREATE INDEX route_idx
>   ON route
>   USING btree
>   (shortname COLLATE pg_catalog."default", id);
>
> I believe that the index has all the data that is needed to obtain the
> results in a single query.
> Is it possible to write the query as a single select and if so how?
>

why not just write it as: select id, (select max(id) from route where id <
1350) as prev, (select min(id) from route where id > 1350) as next from
route where id=2; ?
That should be much more efficient for a larger table as it should avoid
the seqscan and allow the index to be used for all 3 numbers.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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

[Tony Theodore]

> On 6 Jan 2016, at 03:47, Joshua D. Drake  wrote:
> 
> In reflection, the only thing a CoC does is put in writing what behaviour we 
> as a project already require, so why not document it and use it as a tool to 
> encourage more contribution to our project?

I fully agree with you. No one would question documenting (or advertising) any 
particular feature - indeed, the quality of documentation is a feature in 
itself. I'm reminded of this 2006 quote from Joss Whedon [1]:

Q: So, why do you write these strong female characters?
A: Because you’re still asking me that question.

The Postgres community is also a great "feature", maybe the question we should 
be asking is - "why isn't it documented yet?”? I don’t see a CoC as an end in 
itself, it’s merely an artefact of a community that is as proud of it’s 
workings as it’s output.

Regards,

Tony

[1] 
http://www.goodreads.com/quotes/1018998-why-aren-t-you-asking-a-hundred-other-guys-why-they



-- 
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?

[Pavel Stehule]

2016-01-06 8:08 GMT+01:00 Oleg Bartunov :

>
>
> On Wed, Jan 6, 2016 at 7:41 AM, Jim Nasby 
> wrote:
>
>> On 1/5/16 10:03 PM, John R Pierce wrote:
>>
>>> On 1/5/2016 5:31 PM, Jim Nasby wrote:
>>>
 IMHO, the real problem here is not simply a CoC, it is that the
 Postgres community doesn't focus on developing the community itself.
 The closest we come to "focus" is occasional talk on -hackers about
 how we need more developers. There is no formal
 discussion/leadership/coordination towards actively building and
 strengthening our community. Until that changes, I fear we will always
 have a lack of developers. More importantly, we will continue to lack
 all the other ways that people could contribute beyond writing code.
 IE: the talk shouldn't be about needing more developers, it should be
 about needing people who want to contribute time to growing the
 community.

>>>
>>>
>>> That sounds like a bunch of modern marketing graduate mumbojumbo to
>>> me.The postgres community are the people who actually support it on
>>> the email lists and IRC, as well as the core development teams, and
>>> INMO, they are quite strong and effective. when you start talking
>>> about social marketing and facebook and twitter and stuff, thats just a
>>> bunch of feelgood smoke and mirrors.The project's output is what
>>> supports it, not having people going out 'growing community', that is
>>> just a bunch of hot air.   you actively 'grow community' when you're
>>> pushing worthless products (soda pop, etc) based on slick marketing
>>> plans rather than actually selling something useful.
>>>
>> .
>> Then why is it that there is almost no contribution to the community
>> other than code and mailing list discussion?
>>
>> Why is the infrastructure team composed entirely of highly experienced
>> code contributors, of which there are ~200 on the planet, when there are
>> literally 100s of thousands (if not millions) of people out there that
>> could do that work (and could probably do it better if it's what they do
>> for a living, no offense to the efforts of the infrastructure team).
>>
>> Why is there a lack of developers? And a serious lack of code reviewers?
>>
>
> I agree with Jim, something is wrong, I see our developers community isn't
> growing and getting older. There is no formal problem to start contribute,
> but steep learning curve and lack of mentoring practice scare people.
>

The CoC doesn't solve it. We do on mature, stable, pretty complex code -
use C (not JavaScript or Java).  This isn't hobby project or student
project.

Taking new developers needs the hard individual work with any potential
developer/student. I see as interesting one point - PostgreSQL
extensibility - the less experienced developer can write extension, there
can be interesting experimental extensions that can be supported without
risk of unstability of core code. Can be nice to allow to write not only C
language extensions. Then the Postgres can be used on universities and in
some startup companies - and it can increase the number of active
developers. My very talented colleague doesn't write to Postgres due C
language. He like to write planner in lisp or erlang. Or like to play in
these languages. C is barrier for younger people.

I don't afraid about community - I see some new faces from Russia and other
countries. There is 1/1 ratio of Postgres developers and Postgres
users. Hard to change it. Almost all developers write code for job not for
hobby.

Regards

Pavel




>
> 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] Failing to known state

[oleg yusim]

Hi Adrian,

Thank you very much for that link. It confirms what JD and John said, plus
explains couple other moments to me.

Thanks,

Oleg

On Tue, Jan 5, 2016 at 7:04 PM, Adrian Klaver 
wrote:

> On 01/05/2016 04:12 PM, oleg yusim wrote:
> > Hi Adrian,
> >
> > I meant a scenario, when user is trying to connect to database (doesn't
> > matter what interface) and database fails at this moment. If all
> > authentication/authorization/validation functions are written to return
> > false in case of abnormal termination, we are fine. If not, we can
> > potentially encounter the situation when database fails into state where
> > user is given greater privileges than he/she should or even
> > authenticated, when he/she shouldn't.
>
> Might want to take a look at:
>
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/postmaster/postmaster.c;h=41dbdf4bf9eeb54ae0a774ab21fc1c1362aa55f9;hb=d25c7d70ff46d1b2f2400f29d100190efe84d70d
>
> /*
>  * CleanupBackend -- cleanup after terminated backend.
>  *
>  * Remove all local state associated with backend.
>  *
>  * If you change this, see also CleanupBackgroundWorker.
>  */
> static void
> CleanupBackend
>
>
> /*
>  * HandleChildCrash -- cleanup after failed backend, bgwriter,
> checkpointer,
>  * walwriter, autovacuum, or background worker.
>  *
>  * The objectives here are to clean up our local state about the child
>  * process, and to signal all other remaining children to quickdie.
>  */
> static void
> HandleChildCrash(in
>
> etc
>
> Just do a find on crash.
>
>
> >
> > Thanks,
> >
> > Oleg
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Roxanne Reid-Bennett]

On 1/5/2016 6:13 PM, Joshua D. Drake wrote:

On 01/05/2016 05:31 PM, Jim Nasby wrote:


Well, that highlights that it's not just about a CoC, it's the things
that surround it. Especially what the conflict resolution policy is.

I suspect JD thought about this because of a recent Facebook thread[1]
about how the FreeBSD community just screwed this up big-time[2]. The
big screw-up was not having solid ways to deal with such complaints in
place. Sadly, as part of that thread, it comes to light that there is
some history of this in the Postgres project as well.


The Facebook post was the secondary catalyst. The primary one was 
discussions I have had on twitter about CoCs as well as continual work 
with various conferences.



What I'd love to see is support and commitment from the Postgres
community to actively attract people who will focus not on the code but
on building the community itself. I know there are people in the
community that would be interested in doing that, but without active
support and some encouragement things aren't going to change.


Since the first PostgreSQL Conference East in Maryland, I have 
requested this. A good portion of the keynote was about this. For some 
reason our community doesn't show a lot of interest.


I'm a relatively quiet observer of the lists (and user of Postgres off 
and on).  Having organized a group of virtual discussion lists many 
moons ago and to help manage the volume of new contributors forced a CoC 
on them which both helped and hurt the community. I personally see no 
problem with a CoC, but am experienced enough to be cautious about the 
implementation.


You implied in your first post that you would attract more contributors 
with a CoC.  Jim Nasby posted links which outline recent issues related 
to harassment.  Other comments in this thread lead me to believe that 
there are other potential perspectives ... Can I ask...


What specific problem or problems does the Postgres community currently 
experience ?
What specific problem or problems might the Postgres community 
experience (that you would like to avoid)?

  [ that has led you to believe having a CoC would solve?  ]


Roxanne


Sincerely,

JD





--
[At other schools] I think the most common fault in general is to teach 
students how to pass exams instead of teaching them the science.
Donald Knuth



--
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] Efficiently selecting single row from a select with window functions row_number, lag and lead

[Jim Nasby]

On 1/1/16 9:39 PM, Andrew Bailey wrote:

select id, row_number() over w as rownum, lag(id, 1) over w as prev,
lead(id, 1) over w as next from route where id=1350 window w as (order
by shortname, id asc rows between 1 preceding and 1 following) order by
shortname, id ;

However this gives the result
1350;1;;

The following query gives the result I am expecting

select * from (select id, row_number() over w as rownum,
lag(id, 1) over w as prev, lead(id, 1) over w as next
from route window w as (order by shortname, id
rows between 1 preceding and 1 following) order by shortname, id) as s
where id=1350

1350;3;1815;1813

The explain plan is
"Subquery Scan on s  (cost=0.14..15.29 rows=1 width=32)"
"  Filter: (s.id  = 1350)"
"  ->  WindowAgg  (cost=0.14..13.51 rows=143 width=12)"
"->  Index Only Scan using route_idx on route  (cost=0.14..10.29
rows=143 width=12)"


I'm pretty sure the issue here is that the WHERE clause is limiting your 
result set before the window can find what you're looking for.


You could probably switch the WHERE in your original query to a HAVING 
and get the same results.


I'm not sure the filter can actually be pushed past the window functions 
to get the result you want. That Index Only Scan could still be pulling 
every row in the table.


BTW, if you switch the order by to id, shortname then it might be able 
to use the index, but of course the results would be different.

--
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] Efficiently selecting single row from a select with window functions row_number, lag and lead

[Vitaly Burovoy]

On 1/1/16, Andrew Bailey  wrote:
> I would like to do the following:
>
> select id, row_number() over w as rownum, lag(id, 1) over w as prev,
> lead(id, 1) over w as next from route where id=1350 window w as (order by
> shortname, id asc rows between 1 preceding and 1 following)  order by
> shortname, id ;
>
> However this gives the result
> 1350;1;;
>
> The following query gives the result I am expecting
>
> select * from (select id, row_number() over w as rownum,
> lag(id, 1) over w as prev, lead(id, 1) over w as next
> from route window w as (order by shortname, id
> rows between 1 preceding and 1 following) order by shortname, id) as s
> where id=1350
>
> 1350;3;1815;1813
>
> The explain plan is
> "Subquery Scan on s  (cost=0.14..15.29 rows=1 width=32)"
> "  Filter: (s.id = 1350)"
> "  ->  WindowAgg  (cost=0.14..13.51 rows=143 width=12)"
> "->  Index Only Scan using route_idx on route  (cost=0.14..10.29
> rows=143 width=12)"
>
> as it makes use of the index created as follows
>
> CREATE INDEX route_idx
>   ON route
>   USING btree
>   (shortname COLLATE pg_catalog."default", id);
>
> I believe that the index has all the data that is needed to obtain the
> results in a single query.
> Is it possible to write the query as a single select and if so how?
>
> Thanks in advance
>
> Andrew Bailey
>

It works as expected.

You can read it at
http://www.postgresql.org/docs/current/static/tutorial-window.html
---
The rows considered by a window function are those of the "virtual
table" produced by the query's FROM clause as filtered by its WHERE,
GROUP BY, and HAVING clauses if any. For example, a row removed
because it does not meet the WHERE condition is not seen by any window
function.
---

So your WHERE clause from the first query selects the only row to a
"virtual table", and lead and lag works with the table contains the
only one row and doesn't have any other before and after it.

In the second query subselect selects ALL rows to the "virtual table",
lead and lag fill values and WHERE in the external select gets a
single row filled by subselect.

It is also in the documentation:
"If there is a need to filter or group rows after the window
calculations are performed, you can use a sub-select."

Unfortunately it is impossible to give an access to window function to
rows not selected by a current query.

-- 
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?

[John R Pierce]

On 1/5/2016 5:31 PM, Jim Nasby wrote:


IMHO, the real problem here is not simply a CoC, it is that the 
Postgres community doesn't focus on developing the community itself. 
The closest we come to "focus" is occasional talk on -hackers about 
how we need more developers. There is no formal 
discussion/leadership/coordination towards actively building and 
strengthening our community. Until that changes, I fear we will always 
have a lack of developers. More importantly, we will continue to lack 
all the other ways that people could contribute beyond writing code. 
IE: the talk shouldn't be about needing more developers, it should be 
about needing people who want to contribute time to growing the 
community. 



That sounds like a bunch of modern marketing graduate mumbojumbo to 
me.The postgres community are the people who actually support it on 
the email lists and IRC, as well as the core development teams, and 
INMO, they are quite strong and effective. when you start talking 
about social marketing and facebook and twitter and stuff, thats just a 
bunch of feelgood smoke and mirrors.The project's output is what 
supports it, not having people going out 'growing community', that is 
just a bunch of hot air.   you actively 'grow community' when you're 
pushing worthless products (soda pop, etc) based on slick marketing 
plans rather than actually selling something useful.





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

[Jim Nasby]

On 1/5/16 10:03 PM, John R Pierce wrote:

On 1/5/2016 5:31 PM, Jim Nasby wrote:

IMHO, the real problem here is not simply a CoC, it is that the
Postgres community doesn't focus on developing the community itself.
The closest we come to "focus" is occasional talk on -hackers about
how we need more developers. There is no formal
discussion/leadership/coordination towards actively building and
strengthening our community. Until that changes, I fear we will always
have a lack of developers. More importantly, we will continue to lack
all the other ways that people could contribute beyond writing code.
IE: the talk shouldn't be about needing more developers, it should be
about needing people who want to contribute time to growing the
community.



That sounds like a bunch of modern marketing graduate mumbojumbo to
me.The postgres community are the people who actually support it on
the email lists and IRC, as well as the core development teams, and
INMO, they are quite strong and effective. when you start talking
about social marketing and facebook and twitter and stuff, thats just a
bunch of feelgood smoke and mirrors.The project's output is what
supports it, not having people going out 'growing community', that is
just a bunch of hot air.   you actively 'grow community' when you're
pushing worthless products (soda pop, etc) based on slick marketing
plans rather than actually selling something useful.


Then why is it that there is almost no contribution to the community 
other than code and mailing list discussion?


Why is the infrastructure team composed entirely of highly experienced 
code contributors, of which there are ~200 on the planet, when there are 
literally 100s of thousands (if not millions) of people out there that 
could do that work (and could probably do it better if it's what they do 
for a living, no offense to the efforts of the infrastructure team).


Why is there a lack of developers? And a serious lack of code reviewers?
--
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] Streaming replication stacked.

[Yoji]

Hi Michael , 

Thank you for your information!!

I understand.
I'll consider to upgrade to 9.4.5.

I'm grateful for all your support.

Yoji 







--
View this message in context: 
http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880550.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

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

[Oleg Bartunov]

On Wed, Jan 6, 2016 at 7:41 AM, Jim Nasby  wrote:

> On 1/5/16 10:03 PM, John R Pierce wrote:
>
>> On 1/5/2016 5:31 PM, Jim Nasby wrote:
>>
>>> IMHO, the real problem here is not simply a CoC, it is that the
>>> Postgres community doesn't focus on developing the community itself.
>>> The closest we come to "focus" is occasional talk on -hackers about
>>> how we need more developers. There is no formal
>>> discussion/leadership/coordination towards actively building and
>>> strengthening our community. Until that changes, I fear we will always
>>> have a lack of developers. More importantly, we will continue to lack
>>> all the other ways that people could contribute beyond writing code.
>>> IE: the talk shouldn't be about needing more developers, it should be
>>> about needing people who want to contribute time to growing the
>>> community.
>>>
>>
>>
>> That sounds like a bunch of modern marketing graduate mumbojumbo to
>> me.The postgres community are the people who actually support it on
>> the email lists and IRC, as well as the core development teams, and
>> INMO, they are quite strong and effective. when you start talking
>> about social marketing and facebook and twitter and stuff, thats just a
>> bunch of feelgood smoke and mirrors.The project's output is what
>> supports it, not having people going out 'growing community', that is
>> just a bunch of hot air.   you actively 'grow community' when you're
>> pushing worthless products (soda pop, etc) based on slick marketing
>> plans rather than actually selling something useful.
>>
>
> Then why is it that there is almost no contribution to the community other
> than code and mailing list discussion?
>
> Why is the infrastructure team composed entirely of highly experienced
> code contributors, of which there are ~200 on the planet, when there are
> literally 100s of thousands (if not millions) of people out there that
> could do that work (and could probably do it better if it's what they do
> for a living, no offense to the efforts of the infrastructure team).
>
> Why is there a lack of developers? And a serious lack of code reviewers?
>

I agree with Jim, something is wrong, I see our developers community isn't
growing and getting older. There is no formal problem to start contribute,
but steep learning curve and lack of mentoring practice scare people.

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] Unique constraints and indexes.

[Tom Lane]

Steve Rogerson  writes:
> Is this a bug? I create a "unique" index, directly but it doesn't add a unique
> constraint. Add a unique constraint and it adds the index and the constraint.

That's operating as designed.  A unique constraint needs an index,
but not vice versa.

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] Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

[Steve Petrie, P.Eng.]

Thanks to Melvin for your remarks. My reply is posted below.

Steve

From: "Melvin Davidson"
Sent: Sunday, January 03, 2016 4:32 PM

More to the point, if you absolutely must
store session data, then why not just do it in a TEMPORARY table, which
will reside in memory and clean itself up when the session ends?

http://www.postgresql.org/docs/9.4/static/sql-createtable.html



Sorry for not having defined the term "session" in my original posting. Here
are quotes from the draft design document, for the proposed session
operations system:

"In this document, the term "session" does not refer to connections with a
postgres server through a postgres client, which connections are referred to
as "sessions" in postgres docs. In this document, the term "session" means a
dialog between an app and the consumer of that app, where the app may or may
not use postgres, for other than its session context data.

"The app that motivates this present proposed design for a postgres-based
session operations facility, is an Internet website application (HTML / HTTP
/ PHP / PostgreSQL) that provides services to a visitor using a web browser.

The "session" under discussion therefore, is used to maintain the
contextual data of the (visitor <=> app) dialog, during which dialog the app
will open and close numerous brief connections with the postgres client,
such connections also being referred to as "sessions" within postgres
docs.

So a TEMPORARY table will not suffice, but using a tablespace in SSHD for
the session operations tables, might yield performance gains like
TEMPORARY..



On Sun, Jan 3, 2016 at 3:43 PM, Pavel Stehule 
wrote:


Hi

2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng. :


*Greetings To Postgres Forum,*

This posting is further to a prior forum thread -- subject "[*GENERAL]
using a postgres table as a multi-writer multi-updater queue*", that was
started on 23 November 2015 by Chris Withers ch...@simplistix.co.uk. I
believe the last posting to that thread was on 1 December 2015 by George
Neuner .

A related thread of interest, was started earlier -- subject *"[GENERAL]
using postgresql for session*",  on 7 October 2015 by John Tiger <
john.tigernas...@gmail.com>.



I am sorry for off topic. But are you sure, so using Postgres for session
data is a good idea? Using Postgres for short living data is can enforce
a
performance problems when a load will be higher.

Regards

Pavel




* * *
* * *

I made some postings to the first above-mentioned thread, as I am
working
to migrate a php website application from mysql to postgres. An
important
objective of this migration is to find a good way to use a postgres
table
to store session context data rows, one row for each active website
visitor.

One item of advice (among much other helpful advice) I took away from
the
first thread mentioned above, was to avoid use of DELETE commands as a
means to recycle session context table row image storage, when a session
is terminated.

To use instead, a TRUNCATE command on an entire session context table,
to
quickly and efficiently recycle session context row image storage space,
back to the filesystem, so the space is immediately available for reuse.

* * *
* * *

Since then, I have been working to design a way to use postgres table(s)
as a session context store, for a simple, reliable and high-performance
"session operations system" (SOS).

A design for a postgres-based SOS, that follows two key principles to
ensure maximum session workload throughput capacity:

*PRINCIPLE #1*: *1.1* Use only the TRUNCATE TABLE command, to recycle
frequently, rapidly and efficiently back to the filesystem, session
context
table storage space occupied by obsolete images of session context rows;
and *1.2* do not use DELETE / AUTOVACUUM / VACUUM commands at all, for
this recycling.

*PRINCIPLE #2*: *2.1* Use sequence generators for various
globally-addressable fast-access "iterators"**, that provide the php
website app (and its PL/pgSQL functions), with e.g. access to an
appropriate individual session context table; *2.2* Access granted to a
table from a pool of session context tables, each pool having its tables
all in the same operational state.

The downside of Principle #1 is the considerable added complexity of
having to manage multiple tables, to store session context data rows.

The downside of Principle #2 is that the sequence generator has no role
in sql transaction / savepoint semantics. So explicit provision for
synchronization is required, adding further complexity.

 (** An "iterator" is derived from a sequence generator, by using excess
unneeded precision in high-order bits of the sequence integer value, to
encode "iterator" metadata -- as an efficient way to make this metadata
available to multiple concurrently executing app execution control flow
paths.)

* * *
* * *

*The purpose of this present email, is to present (in pseudocode) for
critque by forum members, 

Re: [GENERAL] Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

[Steve Petrie, P.Eng.]

Thanks to Pavel for your remarks. My reply is posted below.

Steve

- Original Message - 
From: "Pavel Stehule" 

To: "Steve Petrie, P.Eng." 
Cc: 
Sent: Sunday, January 03, 2016 3:43 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?



Hi

2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng. :


*Greetings To Postgres Forum,*

This posting is further to a prior forum thread -- subject "[*GENERAL]
using a postgres table as a multi-writer multi-updater queue*", that was
started on 23 November 2015 by Chris Withers ch...@simplistix.co.uk. I
believe the last posting to that thread was on 1 December 2015 by George
Neuner .

A related thread of interest, was started earlier -- subject *"[GENERAL]
using postgresql for session*",  on 7 October 2015 by John Tiger <
john.tigernas...@gmail.com>.



I am sorry for off topic. But are you sure, so using Postgres for session
data is a good idea? Using Postgres for short living data is can enforce a
performance problems when a load will be higher.



You are not off topic. Not at all. The objective is to find a design for a
session operations system based on postgres, that eliminates performance 
problems

under high load, that you mention.

Get the benefits of using postgres as the session context store
facility. But use postgres in a way that is optimized for handling smoothly
and efficiently, large workload volumes of  INSERT / SELECT / UPDATE
commands, on short-lived transient session data rows,
under long intense bursts of app session activity.


Regards

Pavel




* * *
* * *

I made some postings to the first above-mentioned thread, as I am working
to migrate a php website application from mysql to postgres. An important
objective of this migration is to find a good way to use a postgres table
to store session context data rows, one row for each active website
visitor.

One item of advice (among much other helpful advice) I took away from the
first thread mentioned above, was to avoid use of DELETE commands as a
means to recycle session context table row image storage, when a session
is terminated.

To use instead, a TRUNCATE command on an entire session context table, to
quickly and efficiently recycle session context row image storage space,
back to the filesystem, so the space is immediately available for reuse.

* * *
* * *

Since then, I have been working to design a way to use postgres table(s)
as a session context store, for a simple, reliable and high-performance
"session operations system" (SOS).

A design for a postgres-based SOS, that follows two key principles to
ensure maximum session workload throughput capacity:

*PRINCIPLE #1*: *1.1* Use only the TRUNCATE TABLE command, to recycle
frequently, rapidly and efficiently back to the filesystem, session
context
table storage space occupied by obsolete images of session context rows;
and *1.2* do not use DELETE / AUTOVACUUM / VACUUM commands at all, for
this recycling.

*PRINCIPLE #2*: *2.1* Use sequence generators for various
globally-addressable fast-access "iterators"**, that provide the php
website app (and its PL/pgSQL functions), with e.g. access to an
appropriate individual session context table; *2.2* Access granted to a
table from a pool of session context tables, each pool having its tables
all in the same operational state.

The downside of Principle #1 is the considerable added complexity of
having to manage multiple tables, to store session context data rows.

The downside of Principle #2 is that the sequence generator has no role
in
sql transaction / savepoint semantics. So explicit provision for
synchronization is required, adding further complexity.

 (** An "iterator" is derived from a sequence generator, by using excess
unneeded precision in high-order bits of the sequence integer value, to
encode "iterator" metadata -- as an efficient way to make this metadata
available to multiple concurrently executing app execution control flow
paths.)

* * *
* * *

*The purpose of this present email, is to present (in pseudocode) for
critque by forum members, a proposed approach to synchronizing use of the
"iterators" (sequence generators) described above, among multiple
concurrent actors, in the website php app session operations scenario.*

Since I am a postgres novice, I am hoping that members of this postgres
forum, will be kind enough to examine and critique the (boiled-down,
simplified) pseudocode for the proposed approach to synchronization.

(In this discussion, the term "process" does not refer specifically to a
"process" as implemented in operating systems, as one form of program
execution control, that is contrasted with "thread" as another form of
program execution control. In this discussion, the term "process" means
the
general sense of any program execution path that can occur in parallel
concurrently with 

Re: [GENERAL] Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

[Steve Petrie, P.Eng.]

Thanks to Adrian for your remarks. My replies are posted below.

Steve

- Original Message - 
From: "Adrian Klaver" 

To: "Melvin Davidson" ; "Pavel Stehule"

Cc: "Steve Petrie, P.Eng." ;

Sent: Sunday, January 03, 2016 4:38 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?



On 01/03/2016 01:32 PM, Melvin Davidson wrote:

As others have pointed out, storing session data in a table is not a
good idea. Even if you use TRUNCATE, you will still not reclaim all the
space used unless you use vacuum full. More to the point, if you


Actually:

http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html

"TRUNCATE quickly removes all rows from a set of tables. It has the same
effect as an unqualified DELETE on each table, but since it does not
actually scan the tables it is faster. Furthermore, it reclaims disk space
immediately, rather than requiring a subsequent VACUUM operation. This is
most useful on large tables."



Good to have this confirmed. TRUNCATE is way faster than DELETE /.
AUTOVACUUM / VACUUM.

So if the session operations system uses a pool (of e.g. 31) session context
tables, it will be feasible periodically to recycle session context row
image storage space back to the filesystem, by means of the hyper-fast table
TRUNCATE command, without ever disrupting the app's access to the session
context data store. Because many other session context tables in the pool of
(e.g. 31) tables, remain online and available to the app, during the
TRUNCATE operation.

For example, session operations could be configured to impose a 3 limit,
on the count of row images stored in a session context table.

If each session context row image occupies e.g. 1000 bytes, then the session
operations system will be recycling 30 MB of storage back to the filesystem,
in one quick table TRUNCATE command. Instead of going through a painstaking
per-row AUTOVACUUM / VACUUM process, that is more suitable for long-lived,
high-value data assets.

The app only ever uses INSERT / SELECT / UPDATE commands on session context
data rows. The app never uses DELETE commands on these rows. Session
operations has other means to ensure, before doing the table TRUNCATE, that
the session context context table contains only "dead" session context row
images.


the rub is:

"TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on,
which blocks all other concurrent operations on the table. When RESTART
IDENTITY is specified, any sequences that are to be restarted are likewise
locked exclusively. If concurrent access to a table is required, then the
DELETE command should be used instead."




My proposal is to turn this disadvantage of the TRUNCATE command (that it
requires an ACCESS EXCLUSIVE lock on the table) into an advantage, by means
of two design ideas:

1. Using a pool (of e.g. 31) session context tables, so that it will be
feasible periodically to recycle large amounts of session context row image
storage space back to the filesystem, by means of the hyper-fast table
TRUNCATE command, without ever disrupting the app's access to the session
context data store. Because many other session context tables in the pool
(of e.g. 31) tables, will always remain online and available to the app,
during the TRUNCATE operation on the one "dead" table, that is briefly
taken offline by session operations to be TRUNCATEd.

2. Using an explicit LOCK TABLE ... IN ACCESS EXCLUSIVE MODE
command, as a means of synchronizing
usage of sequence generators, with use of TRANSACTION /
SAVEPOINT semantics. The idea is to use a number of sequence generators as
quick-access "iterators"** that are globally-addressable by the app, to
assist efficient orderly usage of individual session context tables by the
app.

(**An "iterator" in this design, piggy-backs iterator fixed-value meta-data,
in the high-order bits of the underlying 64-bit integer sequence generator.
High-order bits that are surplus to the amount of integer precision
required, for the incrementing numeric range needed by the iterator.)

And the objective of my original posting, was to ask this forum to critique
pseudocode that I propose, for achieving the synchronization envisioned in
point 2, above, to coordinate use of sequence generators with SQL
transaction / savepoint semantics. The author being a postgres novice, and a
barely-scratching-the-surface SQL app developer (but an experienced software
engineer).


absolutely must store session data, then why not just do it in a
TEMPORARY table, which will reside in memory and clean itself up when
the session ends?

http://www.postgresql.org/docs/9.4/static/sql-createtable.html


On Sun, Jan 3, 2016 at 3:43 PM, Pavel Stehule > wrote:

Hi

2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng.
 

Re: [GENERAL] Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

[Steve Petrie, P.Eng.]

Thanks to John for your remarks. My reply is posted below.

Steve

- Original Message - 
From: "John R Pierce" 

To: 
Sent: Sunday, January 03, 2016 4:40 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?



On 1/3/2016 1:32 PM, Melvin Davidson wrote:

As others have pointed out, storing session data in a table is not a good
idea. Even if you use TRUNCATE, you will still not reclaim all the space
used unless you use vacuum full. More to the point, if you absolutely
must store session data, then why not just do it in a TEMPORARY table,
which will reside in memory and clean itself up when the session ends?


The fundamental problem is that HTTP itself is stateless, and user
sessions have to be enforced by the web app, with the aid of browser
cookies.   you can't simply directly link a http connection to a SQL
connection and assume thats a session.



Please see my reply to Melvin Davidson.

The "session" I mean is the one that you point out, that has to be enforced
by the web app, to provide continuity missing from the stateless HTTP
protocol.

Except that I do not use browser cookies.

Instead of browser cookies, I use a parameter (session ID key) embedded in
the HTML page served by the app to the consumer's web browser.

And this parameter goes on a round-trip journey, back to the app
program that is launched by the HTTP server, when the visitor actions the
web page submit button and their browser sends an HTTP request message.

The format of the (32-character string) session ID key parameter is:

  CC:MMDDHHMMSS:TT

where:

1. CC --  "quasi-unique" random session identification code
(10-char, hex-encoded), a hash value (e.g. substring of MD5) but NOT a hash
of a password to be remembered and reused, just a randomized value from
x00 to xFF (decimal: 0 ... 1099511627775);

2. MMDDHHMMSS -- session initiation time (14-char, decimal-encoded) e.g.
20160105040427;

3.  -- serial number of corresponding session context row when first
INSERTed (4-char, hex-encoded) value from x0001 to x (decimal: 1 ...
65535);

4. TT -- serial number of current session context table where session row is
stored (2-char, hex-encoded) value from x01 to x1F (decimal: 1 ... 31);

Here is a paste of example HTML:



  
  

  
   

   

...


In the above HTML, hidden field name="a"
value="B37000AC2C:20160105040427:0AD11A" is a session ID key input parameter
to PHP program eto_session_act_2a.php, that interprets parameter "a" as:

1. CC == "B37000AC2C";
2. MMDDHHMMSS == "20160105040427";
3.  == "0AD1" (decimal 2769);
4. TT == "1A" (decimal 26);


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




--
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] Regex "embedded options" does only work on the whole pattern?

[matshyeq]

Makes sense.
Thank you both for clarifications!
Was only wondering if this feature is there so I could elegantly do
equivalent of perl's (small 'a', anycase 'sd', small 'f'):

$,="\n";
my $testr='1asdf
2AsdF
3AsDF
4asDf
5aSDf
6aSdf
7ASdf
8Asdf';
my @res = $testr =~ /a(?i:sd)f/g;
print @res;
-
asdf
asDf
aSDf
aSdf

Thank you,
Kind Regards
~Maciek

On 4 January 2016 at 03:58, Tom Lane  wrote:

> matshyeq  writes:
> > I can see postgresql claims to supports regular expression flags:
>
> Yup.
>
> > What I find don't makes sense to me is that those flags could be used to
> > switch on/off match feature locally as opposed to the whole regex (same
> as
> > though flags parameters of regex functions).
>
> Not all of them would make sense locally; in fact I'd venture that
> case-sensitivity is the *only* flag that anyone would consider using
> that way.
>
> > Perl supports that locality while postgres documentation says:
> > "*An ARE can begin with embedded options: a sequence (?xyz) (where xyz is
> > one or more alphabetic characters) specifies options affecting the rest
> of
> > the RE.
>
> Right.  It says "begin with" and it means "begin with".
>
> We are not Perl and are not attempting to be bug-compatible with its regex
> engine.  If you want bug-compatibility, see PL/Perl.
>
> regards, tom lane
>

Re: [GENERAL] A unique pairs version of UNNEST() ?

[Kevin Grittner]

On Tue, Jan 5, 2016 at 1:59 AM, Alban Hertroys  wrote:

> with list_of_ids as (
>   select unnest(list_of_ids) as id from table
> )
> select a.id, b.id
>   from list_of_ids a, list_of_ids b
>  where b.id > a.id;

Or, to morph this to array output (which the OP seemed to want):

test=# with list_of_ids as (
test(#   select unnest('{1,2,3,4,5}'::int[]) as id
test(# )
test-# select array [a.id, b.id]
test-#   from list_of_ids a, list_of_ids b
test-#  where b.id > a.id;
 array
---
 {1,2}
 {1,3}
 {1,4}
 {1,5}
 {2,3}
 {2,4}
 {2,5}
 {3,4}
 {3,5}
 {4,5}
(10 rows)

Nothing in that not already mentioned; just putting it all
together.

The OP mentioned wanting a count, but that wasn't too clear to me;
using a window function to number the rows, changing the comparison
from > to >= while excluding self-matches should make that pretty
easy.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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/05/2016 11:08 AM, Roland van Laar wrote:



On January 5, 2016 5:47:16 PM GMT+01:00, "Joshua D. Drake" 
 wrote:

Hello,

I had a hard time writing this email. I think Code of Conducts are
non-essential, a waste of respectful people's time and frankly if you
are going to be a jerk, our community will call you out on it.
Unfortunately a lot of people don't agree with that. I have over the
course of the last year seen more and more potential users very
explicitly say, "I will not contribute to a project or attend a
conference that does not have a CoC".


Do they give a rational for that?


I don't think I am a good person to rationalize their reasoning because 
I don't like the idea of a CoC. That said, I think a lot of boils down 
to perception, responsibility, accountability and the fact that a lot of 
people are flat out jerks. I am not talking the ball busting type of 
jerk but honest, just not nice people or people who vastly lack the 
ability to integrate with larger society. Those people tend to need 
guidelines for their jerkiness because they will say, "I didn't know I 
couldn't do/say XYZ". Whether that is true or not, I have no idea.



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

[GENERAL] select and count efficiency (~35 mln rows)

[Grzegorz Kuczera]

This is my first question here, so I would like to say hello to everyone:)

In my case, the problem appears when I want to fetch some data to 
inflate the table with it. First of all, I am counting the records from 
the table (for paging, over indexed column), then the select query is 
performed (with limit equal to 15).
I use the c3p0 library to manage the pool connections to the database 
and what I get is the timeout: both queries (separately) can take longer 
than 150 seconds. If I execute the query straight on the database 
(without usage of jdbc), it sometimes takes even 11 minutes to complete.


Month or two ago I vacuumed the database manually. Now I can see - in 
the last_vacuum and last_autovacuum from the pg_stat_all_tables - that 
the nulls are written there.


There are about 35 milion records in the table.

Details:

 * version - PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by
   gcc (Debian 4.7.2-5) 4.7.2, 64-bit
 * result of select pg_postmaster_start_time() - 2015-12-22
   12:03:55.471436+01


And here is the question, which I posted on the Stackoverflow two months 
ago:

http://stackoverflow.com/questions/33009865/why-index-only-scan-is-taking-so-long

I would appreciate any help/tips.


smime.p7s
Description: Kryptograficzna sygnatura S/MIME

Re: [GENERAL] select and count efficiency (~35 mln rows)

[Andreas Kretschmer]

Grzegorz Kuczera  wrote:

> This is my first question here, so I would like to say hello to everyone:)
> 
> In my case, the problem appears when I want to fetch some data to inflate the
> table with it. First of all, I am counting the records from the table (for
> paging, over indexed column), then the select query is performed (with limit
> equal to 15).

tl;dr.

Please read the links below:

http://www.depesz.com/2007/08/29/better-results-paging-in-postgresql-82/
https://wiki.postgresql.org/wiki/File:Pagination_Done_the_PostgreSQL_Way.pdf
https://coderwall.com/p/lkcaag

> 
> There are about 35 milion records in the table.

do you really want to show all records, even with pagination? REALLY?

> 
> And here is the question, which I posted on the Stackoverflow two months ago:
>  http://stackoverflow.com/questions/33009865/
> why-index-only-scan-is-taking-so-long


Because of MVCC / visibility a seq-scan is requisite.

For a guestimate about the number of rows you can look into pg_class.



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

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

[Michael Paquier]

On Wed, Jan 6, 2016 at 6:09 AM, Joshua D. Drake  wrote:
> On 01/05/2016 11:08 AM, Roland van Laar wrote:
>> On January 5, 2016 5:47:16 PM GMT+01:00, "Joshua D. Drake"
>>  wrote:
>>> I had a hard time writing this email. I think Code of Conducts are
>>> non-essential, a waste of respectful people's time and frankly if you
>>> are going to be a jerk, our community will call you out on it.
>>> Unfortunately a lot of people don't agree with that. I have over the
>>> course of the last year seen more and more potential users very
>>> explicitly say, "I will not contribute to a project or attend a
>>> conference that does not have a CoC".
>>
>>
>> Do they give a rational for that?
>
>
> I don't think I am a good person to rationalize their reasoning because I
> don't like the idea of a CoC. That said, I think a lot of boils down to
> perception, responsibility, accountability and the fact that a lot of people
> are flat out jerks. I am not talking the ball busting type of jerk but
> honest, just not nice people or people who vastly lack the ability to
> integrate with larger society. Those people tend to need guidelines for
> their jerkiness because they will say, "I didn't know I couldn't do/say
> XYZ". Whether that is true or not, I have no idea.

Being a jerk is not a problem that can be solved with a code of
conduct, just something that each individual should try to solve by
himself. And IMHO, this would just complicate the contribution flow
and the life of people who could potentially provide something useful.
Folks on the mailing lists here are really cool and it is possible to
have really nice and constructive conversations on many topics anyway,
the presence of a CoC is not going to change that, and that's what
matters.
-- 
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] Failing to known state

[oleg yusim]

Hi Joe,

Exactly how I marked it :)

Thanks,

Oleg

On Tue, Jan 5, 2016 at 6:50 PM, Joe Conway  wrote:

> On 01/05/2016 04:32 PM, John R Pierce wrote:
> > On 1/5/2016 4:12 PM, oleg yusim wrote:
> >> I meant a scenario, when user is trying to connect to database
> >> (doesn't matter what interface) and database fails at this moment. If
> >> all authentication/authorization/validation functions are written to
> >> return false in case of abnormal termination, we are fine. If not, we
> >> can potentially encounter the situation when database fails into state
> >> where user is given greater privileges than he/she should or even
> >> authenticated, when he/she shouldn't.
> >
> > if the postgres server processes terminate for any reason, there's
> > nothing to connect to.  the client application will get a error like
> > 'connection refused' back from the connection attempt, or if it was
> > already connected and the server aborts, the next query will return an
> > error like CONNECTION_BAD.  there's no possible privilege elevation.
>
> +1
>
> I think you can call this one "Applicable -- Inherently Meets"
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

Re: [GENERAL] Failing to known state

[Adrian Klaver]

On 01/05/2016 04:12 PM, oleg yusim wrote:
> Hi Adrian,
> 
> I meant a scenario, when user is trying to connect to database (doesn't 
> matter what interface) and database fails at this moment. If all 
> authentication/authorization/validation functions are written to return 
> false in case of abnormal termination, we are fine. If not, we can 
> potentially encounter the situation when database fails into state where 
> user is given greater privileges than he/she should or even 
> authenticated, when he/she shouldn't.

Might want to take a look at:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/postmaster/postmaster.c;h=41dbdf4bf9eeb54ae0a774ab21fc1c1362aa55f9;hb=d25c7d70ff46d1b2f2400f29d100190efe84d70d

/*
 * CleanupBackend -- cleanup after terminated backend.
 *
 * Remove all local state associated with backend.
 *
 * If you change this, see also CleanupBackgroundWorker.
 */
static void
CleanupBackend


/*
 * HandleChildCrash -- cleanup after failed backend, bgwriter, checkpointer,
 * walwriter, autovacuum, or background worker.
 *
 * The objectives here are to clean up our local state about the child
 * process, and to signal all other remaining children to quickdie.
 */
static void
HandleChildCrash(in

etc

Just do a find on crash.


> 
> Thanks,
> 
> Oleg
> 



-- 
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] Failing to known state

[oleg yusim]

John,

Thanks, what you are saying makes sense. I agree, it would cause all user
to go through authentication/authorization loop all over and terminate all
running transactions too.

Thanks,

Oleg

On Tue, Jan 5, 2016 at 6:32 PM, John R Pierce  wrote:

> On 1/5/2016 4:12 PM, oleg yusim wrote:
>
> I meant a scenario, when user is trying to connect to database (doesn't
> matter what interface) and database fails at this moment. If all
> authentication/authorization/validation functions are written to return
> false in case of abnormal termination, we are fine. If not, we can
> potentially encounter the situation when database fails into state where
> user is given greater privileges than he/she should or even authenticated,
> when he/she shouldn't.
>
>
>
>
>
> if the postgres server processes terminate for any reason, there's nothing
> to connect to.  the client application will get a error like
> 'connection refused' back from the connection attempt, or if it was already
> connected and the server aborts, the next query will return an error like
> CONNECTION_BAD.  there's no possible privilege elevation.
>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>

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

[Jim Nasby]

On 1/5/16 6:32 PM, Scott Marlowe wrote:

I don't think I am a good person to rationalize their reasoning because I
>don't like the idea of a CoC. That said, I think a lot of boils down to
>perception, responsibility, accountability and the fact that a lot of people
>are flat out jerks. I am not talking the ball busting type of jerk but
>honest, just not nice people or people who vastly lack the ability to
>integrate with larger society. Those people tend to need guidelines for
>their jerkiness because they will say, "I didn't know I couldn't do/say
>XYZ". Whether that is true or not, I have no idea.

CoC:
1: Use our code how you want
2: Don't sue us
3: Don't be a jerk


Well, that highlights that it's not just about a CoC, it's the things 
that surround it. Especially what the conflict resolution policy is.


I suspect JD thought about this because of a recent Facebook thread[1] 
about how the FreeBSD community just screwed this up big-time[2]. The 
big screw-up was not having solid ways to deal with such complaints in 
place. Sadly, as part of that thread, it comes to light that there is 
some history of this in the Postgres project as well.


IMHO, the real problem here is not simply a CoC, it is that the Postgres 
community doesn't focus on developing the community itself. The closest 
we come to "focus" is occasional talk on -hackers about how we need more 
developers. There is no formal discussion/leadership/coordination 
towards actively building and strengthening our community. Until that 
changes, I fear we will always have a lack of developers. More 
importantly, we will continue to lack all the other ways that people 
could contribute beyond writing code. IE: the talk shouldn't be about 
needing more developers, it should be about needing people who want to 
contribute time to growing the community.


I saw a great presentation about building a strong community by Joan 
Touzet of CouchDB. The presentation link is currently down, but there's 
a great interview with her at [3]. CouchDB didn't focus on community 
building until they had a major problem to deal with. Now, they make 
community one of their focal points. Just one example, this is the 3rd 
paragraph on their home page:


"We welcome your contributions. CouchDB is an open source project. 
Everything, from this website to the core of the database itself, has 
been contributed by helpful individuals. The time and attention of our 
contributors is our most precious resource, and we always need more of 
it. Our primary goal is to build a welcoming, supporting, inclusive and 
diverse community. We abide by Code of Conduct and a set of Project 
Bylaws. Come join us!"


What I'd love to see is support and commitment from the Postgres 
community to actively attract people who will focus not on the code but 
on building the community itself. I know there are people in the 
community that would be interested in doing that, but without active 
support and some encouragement things aren't going to change.


[1] https://www.facebook.com/jon.erdman.jr/posts/10153828693183899
[2] 
http://blog.randi.io/2015/12/31/the-developer-formerly-known-as-freebsdgirl/

[3] https://opensource.com/life/15/8/couchdb-community-apache-way
--
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] Failing to known state

[oleg yusim]

Thanks JD.

Let me confirm I got you right. So, by exception you mean the
authentication/authorization/validation functions would return false in
case of DB failure?

Thanks,

Oleg


On Tue, Jan 5, 2016 at 5:33 PM, Joshua D. Drake 
wrote:

> On 01/05/2016 03:21 PM, oleg yusim wrote:
>
>> Thanks JD.
>>
>>  From what I read about WAL (you have been referring to this:
>> http://www.postgresql.org/docs/current/static/wal-internals.html
>> pg_xlog, right?) it allows us to know what happened, but does it
>> warranty known secure state? I mean, I do not think it would help with
>> this:
>>
>> "In general, security mechanisms should be designed so that a failure
>> will follow the same execution path as disallowing the operation. For
>> example, application security methods, such as isAuthorized(),
>> isAuthenticated(), and validate(), should all return false if there is
>> an exception during processing. If security controls can throw
>> exceptions, they must be very clear about exactly what that condition
>> means. "
>>
>
> You are correct, that isn't the pg_xlog but yes, PostgreSQL will throw an
> exception in those types of cases.
>
> Sincerely,
>
> 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.
>

Re: [GENERAL] Failing to known state

[oleg yusim]

Hi Adrian,

I meant a scenario, when user is trying to connect to database (doesn't
matter what interface) and database fails at this moment. If all
authentication/authorization/validation functions are written to return
false in case of abnormal termination, we are fine. If not, we can
potentially encounter the situation when database fails into state where
user is given greater privileges than he/she should or even authenticated,
when he/she shouldn't.

Thanks,

Oleg

On Tue, Jan 5, 2016 at 5:34 PM, Adrian Klaver 
wrote:

> On 01/05/2016 03:21 PM, oleg yusim wrote:
>
>> Thanks JD.
>>
>>  From what I read about WAL (you have been referring to this:
>> http://www.postgresql.org/docs/current/static/wal-internals.html
>> pg_xlog, right?) it allows us to know what happened, but does it
>> warranty known secure state? I mean, I do not think it would help with
>> this:
>>
>> "In general, security mechanisms should be designed so that a failure
>> will follow the same execution path as disallowing the operation. For
>> example, application security methods, such as isAuthorized(),
>> isAuthenticated(), and validate(), should all return false if there is
>> an exception during processing. If security controls can throw
>> exceptions, they must be very clear about exactly what that condition
>> means. "
>>
>
> Not sure what you are talking about above. the application as in the
> client application connecting to the database or the database application
> itself?
>
>
>> Right?
>>
>> Thanks,
>>
>> Oleg
>>
>>
>> On Tue, Jan 5, 2016 at 5:14 PM, Joshua D. Drake > > wrote:
>>
>> On 01/05/2016 03:09 PM, oleg yusim wrote:
>>
>>
>>
>> The question here, what is PostreSQL 9.4.5 (hosted on Linux box)
>> behavior? Does it fail to known/secure state in these 3 cases? I
>> tried
>> to find the description of the way PostgreSQL fails in this
>> regard, but
>> didn't find much.
>>
>>
>> Based on what you pasted, PostgreSQL does fail to a known state.
>> That is the whole point of the xlog.
>>
>> Sincerely,
>>
>> JD
>>
>>
>> Thanks,
>>
>> Oleg
>>
>>
>>
>> --
>> 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.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Streaming replication stacked.

[Yoji]

Hi Andreas and Michael,

Thank you for your information!

Let me know, which should I choose update to 9.4.5 or hot_standby_feedback
off?

Best regards. 


Yoji



--
View this message in context: 
http://postgresql.nabble.com/Streaming-replication-stacked-tp5880104p5880487.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Failing to known state

[John R Pierce]

On 1/5/2016 4:12 PM, oleg yusim wrote:
I meant a scenario, when user is trying to connect to database 
(doesn't matter what interface) and database fails at this moment. If 
all authentication/authorization/validation functions are written to 
return false in case of abnormal termination, we are fine. If not, we 
can potentially encounter the situation when database fails into state 
where user is given greater privileges than he/she should or even 
authenticated, when he/she shouldn't.





if the postgres server processes terminate for any reason, there's 
nothing to connect to.  the client application will get a error like 
'connection refused' back from the connection attempt, or if it was 
already connected and the server aborts, the next query will return an 
error like CONNECTION_BAD.  there's no possible privilege elevation.






--
john r pierce, recycling bits in santa cruz

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

[Scott Marlowe]

On Tue, Jan 5, 2016 at 2:09 PM, Joshua D. Drake  wrote:
> On 01/05/2016 11:08 AM, Roland van Laar wrote:
>>
>>
>>
>> On January 5, 2016 5:47:16 PM GMT+01:00, "Joshua D. Drake"
>>  wrote:
>>>
>>> Hello,
>>>
>>> I had a hard time writing this email. I think Code of Conducts are
>>> non-essential, a waste of respectful people's time and frankly if you
>>> are going to be a jerk, our community will call you out on it.
>>> Unfortunately a lot of people don't agree with that. I have over the
>>> course of the last year seen more and more potential users very
>>> explicitly say, "I will not contribute to a project or attend a
>>> conference that does not have a CoC".
>>
>>
>> Do they give a rational for that?
>
>
> I don't think I am a good person to rationalize their reasoning because I
> don't like the idea of a CoC. That said, I think a lot of boils down to
> perception, responsibility, accountability and the fact that a lot of people
> are flat out jerks. I am not talking the ball busting type of jerk but
> honest, just not nice people or people who vastly lack the ability to
> integrate with larger society. Those people tend to need guidelines for
> their jerkiness because they will say, "I didn't know I couldn't do/say
> XYZ". Whether that is true or not, I have no idea.

CoC:
1: Use our code how you want
2: Don't sue us
3: Don't be a jerk

done.


-- 
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] [SQL] plv8 installation problem

[Adrian Klaver]

On 01/05/2016 09:17 AM, Belju Paul wrote:

Please include list in replies.


Yes plv8.dll exists. I found plv8 downloads for win 32 and for win 64
bit. But there is a difference in file count in bin folder. The exe file
d8.exe is absent in  win32 bit down load file.


I downloaded and installed the EDB 32bit Postgres 9.4.5 on an XP 
machine. I have yet to find the plv8 extension download, so can you 
provide a link to where you got it from?




Belju Paul

On Jan 5, 2016 10:38 PM, "Adrian Klaver" > wrote:

On 01/05/2016 09:02 AM, Belju Paul wrote:

Ccin list

Both Postgre 9.4 and plv8 are downloaded from postgre site


So does plv8.dll exist?


Belju Paul

On Jan 5, 2016 9:30 PM, "Adrian Klaver"

>> wrote:

 On 01/05/2016 12:56 AM, Belju Paul wrote:

 Hi,

 "PostgreSQL 9.4 plv8 32-bit download - PL/V8 1.4.2ish" not
 working in my
 Windows XP 32-Bit OS with PostgreSQL 9.4. While running
"CREATE
 EXTENSION plv8" shows the error could not load library
plv8.dll.
 Help me
 to work plv8 in WinXP platform.


 Well first XP is a no longer supported OS so it is possible
this is
 not fixable. Still it is worth a shot. Before we can start
though we
 will need some more information:

 1) How did you install Postgres 9.4 to begin with?

 2) Where did you get the plv8 download and how did you
install it?

 3) Does plv8.dll exist on the system?


 Thanks & Regards
 Belju Paul
 9447732043



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



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




--
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] Unique constraints and indexes.

[David G. Johnston]

On Tuesday, January 5, 2016, Steve Rogerson 
wrote:

> On 05/01/16 19:47, Tom Lane wrote:
> > Steve Rogerson > writes:
> >> Is this a bug? I create a "unique" index, directly but it doesn't add a
> unique
> >> constraint. Add a unique constraint and it adds the index and the
> constraint.
> >
> > That's operating as designed.  A unique constraint needs an index,
> > but not vice versa.
>
>
> I can see that might be plausible , hence the question but as a "unique
> index"
> imposes as constraint they seem equivalent. What's the functional
> difference
> between the two situations?
>
>
I suspect it has to do with partial unique indexes.

David J.

Re: [GENERAL] Failing to known state

[Joe Conway]

On 01/05/2016 04:32 PM, John R Pierce wrote:
> On 1/5/2016 4:12 PM, oleg yusim wrote:
>> I meant a scenario, when user is trying to connect to database
>> (doesn't matter what interface) and database fails at this moment. If
>> all authentication/authorization/validation functions are written to
>> return false in case of abnormal termination, we are fine. If not, we
>> can potentially encounter the situation when database fails into state
>> where user is given greater privileges than he/she should or even
>> authenticated, when he/she shouldn't.
> 
> if the postgres server processes terminate for any reason, there's
> nothing to connect to.  the client application will get a error like
> 'connection refused' back from the connection attempt, or if it was
> already connected and the server aborts, the next query will return an
> error like CONNECTION_BAD.  there's no possible privilege elevation.

+1

I think you can call this one "Applicable -- Inherently Meets"

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

[GENERAL] Failing to known state

[oleg yusim]

Greetings,

One more security requirement I'm battling with:



The DBMS must fail to a secure state if system initialization fails,
shutdown fails, or aborts fail.

Failure to a known state can address safety or security in accordance with
the mission/business needs of the organization.

Failure to a known secure state helps prevent a loss of confidentiality,
integrity, or availability in the event of a failure of the information
system or a component of the system.

Failure to a known safe state helps prevent systems from failing to a state
that may cause loss of data or unauthorized access to system resources.
Systems that fail suddenly and with no incorporated failure state planning
may leave the hosting system available but with a reduced security
protection capability. Preserving information system state data also
facilitates system restart and return to the operational mode of the
organization with less disruption of mission/business processes.

Databases must fail to a known consistent state. Transactions must be
successfully completed or rolled back.

In general, security mechanisms should be designed so that a failure will
follow the same execution path as disallowing the operation. For example,
application security methods, such as isAuthorized(), isAuthenticated(),
and validate(), should all return false if there is an exception during
processing. If security controls can throw exceptions, they must be very
clear about exactly what that condition means.

Abort refers to stopping a program or function before it has finished
naturally. The term abort refers to both requested and unexpected
terminations.



The question here, what is PostreSQL 9.4.5 (hosted on Linux box) behavior?
Does it fail to known/secure state in these 3 cases? I tried to find the
description of the way PostgreSQL fails in this regard, but didn't find
much.

Thanks,

Oleg

Re: [GENERAL] Failing to known state

[oleg yusim]

Thanks JD.

>From what I read about WAL (you have been referring to this:
http://www.postgresql.org/docs/current/static/wal-internals.html pg_xlog,
right?) it allows us to know what happened, but does it warranty known
secure state? I mean, I do not think it would help with this:

"In general, security mechanisms should be designed so that a failure will
follow the same execution path as disallowing the operation. For example,
application security methods, such as isAuthorized(), isAuthenticated(),
and validate(), should all return false if there is an exception during
processing. If security controls can throw exceptions, they must be very
clear about exactly what that condition means. "

Right?

Thanks,

Oleg



On Tue, Jan 5, 2016 at 5:14 PM, Joshua D. Drake 
wrote:

> On 01/05/2016 03:09 PM, oleg yusim wrote:
>
>
>>
>> The question here, what is PostreSQL 9.4.5 (hosted on Linux box)
>> behavior? Does it fail to known/secure state in these 3 cases? I tried
>> to find the description of the way PostgreSQL fails in this regard, but
>> didn't find much.
>>
>>
> Based on what you pasted, PostgreSQL does fail to a known state. That is
> the whole point of the xlog.
>
> Sincerely,
>
> JD
>
>
> Thanks,
>>
>> Oleg
>>
>
>
> --
> 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.
>

Re: [GENERAL] Failing to known state

[Joshua D. Drake]

On 01/05/2016 03:21 PM, oleg yusim wrote:

Thanks JD.

 From what I read about WAL (you have been referring to this:
http://www.postgresql.org/docs/current/static/wal-internals.html
pg_xlog, right?) it allows us to know what happened, but does it
warranty known secure state? I mean, I do not think it would help with this:

"In general, security mechanisms should be designed so that a failure
will follow the same execution path as disallowing the operation. For
example, application security methods, such as isAuthorized(),
isAuthenticated(), and validate(), should all return false if there is
an exception during processing. If security controls can throw
exceptions, they must be very clear about exactly what that condition
means. "


You are correct, that isn't the pg_xlog but yes, PostgreSQL will throw 
an exception in those types of cases.


Sincerely,

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] Failing to known state

[Joshua D. Drake]

On 01/05/2016 03:09 PM, oleg yusim wrote:




The question here, what is PostreSQL 9.4.5 (hosted on Linux box)
behavior? Does it fail to known/secure state in these 3 cases? I tried
to find the description of the way PostgreSQL fails in this regard, but
didn't find much.



Based on what you pasted, PostgreSQL does fail to a known state. That is 
the whole point of the xlog.


Sincerely,

JD



Thanks,

Oleg



--
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] Failing to known state

[Adrian Klaver]

On 01/05/2016 03:21 PM, oleg yusim wrote:

Thanks JD.

 From what I read about WAL (you have been referring to this:
http://www.postgresql.org/docs/current/static/wal-internals.html
pg_xlog, right?) it allows us to know what happened, but does it
warranty known secure state? I mean, I do not think it would help with this:

"In general, security mechanisms should be designed so that a failure
will follow the same execution path as disallowing the operation. For
example, application security methods, such as isAuthorized(),
isAuthenticated(), and validate(), should all return false if there is
an exception during processing. If security controls can throw
exceptions, they must be very clear about exactly what that condition
means. "


Not sure what you are talking about above. the application as in the 
client application connecting to the database or the database 
application itself?




Right?

Thanks,

Oleg


On Tue, Jan 5, 2016 at 5:14 PM, Joshua D. Drake > wrote:

On 01/05/2016 03:09 PM, oleg yusim wrote:



The question here, what is PostreSQL 9.4.5 (hosted on Linux box)
behavior? Does it fail to known/secure state in these 3 cases? I
tried
to find the description of the way PostgreSQL fails in this
regard, but
didn't find much.


Based on what you pasted, PostgreSQL does fail to a known state.
That is the whole point of the xlog.

Sincerely,

JD


Thanks,

Oleg



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





--
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] Streaming replication stacked.

[Michael Paquier]

On Tue, Jan 5, 2016 at 5:13 PM, Yoji  wrote:
> I'm using version of 9.4.3.

You should update to 9.4.5 as soon as possible.

> And I have seen same behavior on  version of 9.3.4.

As Andreas has already outlined, as hot_standby_feedback is enabled
the master has to wait for the slave and the slave cannot replay from
the master as transactions are running on the hot standby.
-- 
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] Streaming replication stacked.

[Andreas Kretschmer]

Michael Paquier  wrote:

> As Andreas has already outlined, as hot_standby_feedback is enabled
> the master has to wait for the slave and the slave cannot replay from
> the master as transactions are running on the hot standby.

Oh, thanks for the confirmation ;-) (wasn't really sure)


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

Re: [GENERAL] Streaming replication stacked.

[Michael Paquier]

On Wed, Jan 6, 2016 at 9:27 AM, Yoji  wrote:
> Hi Andreas and Michael,
>
> Thank you for your information!
>
> Let me know, which should I choose update to 9.4.5

Updating to 9.4.5 is highly recommended, and a separate issue.

> or hot_standby_feedback off?

Switching hot_standby_feedback to off depends on if you can afford the
fact that the application querying the standby may fail because of
recovery conflicts. There is no magic solution here.
-- 
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] Code of Conduct: Is it time?

[Joshua D. Drake]

On 01/05/2016 05:31 PM, Jim Nasby wrote:


Well, that highlights that it's not just about a CoC, it's the things
that surround it. Especially what the conflict resolution policy is.

I suspect JD thought about this because of a recent Facebook thread[1]
about how the FreeBSD community just screwed this up big-time[2]. The
big screw-up was not having solid ways to deal with such complaints in
place. Sadly, as part of that thread, it comes to light that there is
some history of this in the Postgres project as well.


The Facebook post was the secondary catalyst. The primary one was 
discussions I have had on twitter about CoCs as well as continual work 
with various conferences.



What I'd love to see is support and commitment from the Postgres
community to actively attract people who will focus not on the code but
on building the community itself. I know there are people in the
community that would be interested in doing that, but without active
support and some encouragement things aren't going to change.


Since the first PostgreSQL Conference East in Maryland, I have requested 
this. A good portion of the keynote was about this. For some reason our 
community doesn't show a lot of interest.


Sincerely,

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] Cannot upgrade from 9.3 to 9.4 using pg_upgrade

[Jim Nasby]

On 1/4/16 7:40 PM, Adrian Klaver wrote:

or even better yet could you post the section of the log above the error?


The server log itself might be useful, especially if full query logging 
was turned on. Dunno how easy/possible that is with pg_upgrade.

--
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] Getting the function definition from oid in before firing the function

[Jim Nasby]

On 1/5/16 8:13 AM, Mohammed Ajil wrote:

Now my problem consists of two steps:
- First I need to decide if the algorithm supports the trigger type
(only of LANGUAGE PLPGSQL).


plpgsql is NOT the only language that supports triggers.


- Second I need to get the SQL command that the function will execute.

What I tried was the following:
In the file trigger.c I have found the location where the function is
called.


I think triggers is the wrong way to approach this; it's full of holes 
(not the least of which is triggers don't fire on SELECT). You'd be much 
better off with an executor hook.


Have you looked at https://github.com/2ndQuadrant/pgaudit?
--
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] to_timestamp alternatives

[Jim Nasby]

On 1/1/16 4:11 AM, Thomas Kellerer wrote:

You only need to provide the text value to be casted (no format mask).
Use only a single expression without those unnecessary parentheses:

 SELECT gmt_date||' '||lpad(gmt_time,8,'0')::timestamp

You don't need the lpad() either:

 SELECT gmt_date||' '||gmt_time::timestamp


I suspect you need to wrap that in (). Even if the parser does the right 
thing there, it'd certainly make the intent a lot clearer.


SELECT (gmt_date || ' ' || gmt_time)::timestamp
--
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] COPY FROM STDIN

[Jim Nasby]

On 1/4/16 12:18 PM, Luke Coldiron wrote:

Is there a way to achieve the performance of the COPY FROM STDIN command
within a C extension function connected to the db connection that called
the C function? I have text that I would like to receive as input to a C
function that contains many COPY command statements in the file that
would be parsed similar to how psql would handle the file but I don't
want to shell out to psql as I need to do all of this work on the db
connection that the function was called from as there are other commands
that I need to perform as well after before and after handling the COPY
commands on this same connection. I would like the unit of work to be
all or nothing and have the performance of the COPY FROM STDIN command
and not break things out into SELECT INTO or INSERT statements for
performance.

Ideally I would like to be able to attach to the calling db connection
via SPI_connect() and then use the libpq library to issue the copy
commands via PQputCopyData, PQputCopyEnd.


C functions can use SPI, so I'm not sure what the issue is?

http://www.postgresql.org/docs/9.5/static/spi.html

(BTW, you'll want to scroll to the bottom of that page...)
--
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