Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-10-01 Thread Carl Sverre 
Thanks for the initial results. Can you check that you are not using super
permissions and are enabling row security when running the test? Super
ignores row security.

Also yes, I forgot to add the policy names, sorry about that.
On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG) <
clavadetsc...@swisspug.org> wrote:

> Hello
>
>
> On 29.09.2018 20:24:45, Adrian Klaver  wrote:
> On 9/28/18 11:35 PM, Carl Sverre wrote:
> > *Context*
> > I am using row-level security along with triggers to implement a pure
> > SQL RBAC implementation. While doing so I encountered a weird behavior
> > between INSERT triggers and SELECT row-level security policies.
> >
> > *Question*
> > I have posted a very detailed question on StackOverflow here:
> >
> https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s
> >
> > For anyone who is just looking for a summary/repro, I am seeing the
> > following behavior:
> >
> > CREATE TABLE a (id TEXT);
> > ALTER TABLE a ENABLE ROW LEVEL SECURITY;
> > ALTER TABLE a FORCE ROW LEVEL SECURITY;
> >
> > CREATE TABLE b (id TEXT);
> >
> > CREATE POLICY ON a FOR SELECT
> > USING (EXISTS(
> > select * from b where a.id = b.id
> > ));
> >
> > CREATE POLICY ON a FOR INSERT
> > WITH CHECK (true);
> >
> > CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
> > BEGIN
> > RAISE NOTICE USING MESSAGE = 'inside trigger handler';
> > INSERT INTO b (id) VALUES (NEW.id);
> > RETURN NEW;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER reproTrigger BEFORE INSERT ON a
> > FOR EACH ROW EXECUTE PROCEDURE reproHandler();
> >
> > INSERT INTO a VALUES ('fails') returning id;
> > NOTICE:  inside trigger handler
> > ERROR:  new row violates row-level security policy for table "a"
> >
> > Rather than the error, I expect that something along these lines should
> > occur instead:
> >
> > 1. A new row ('fails') is staged for INSERT
> > 2. The BEFORE trigger fires with NEW set to the new row
> > 3. The row ('fails') is inserted into b and returned from the trigger
> > procedure unchanged
> > 4. The INSERT's WITH CHECK policy true is evaluated to true
> > 5. The SELECT's USING policy select * from b where a.id =
> > b.id is evaluated.  *This should return true due to step 3*
>
> > 6. Having passed all policies, the row ('fails') is inserted in table
> > 7. The id (fails) of the inserted row is returned
> >
> > If anyone can point me in the right direction I would be extremely
> thankful.
>
> When I tried to reproduce the above I got:
>
> test=# CREATE POLICY ON a FOR SELECT
> test-# USING (EXISTS(
> test(# select * from b where a.id = b.id
> test(# ));
> ERROR: syntax error at or near "ON"
> LINE 1: CREATE POLICY ON a FOR SELECT
> ^
> test=#
> test=# CREATE POLICY ON a FOR INSERT
> test-# WITH CHECK (true);
> ERROR: syntax error at or near "ON"
> LINE 1: CREATE POLICY ON a FOR INSERT
>
> Changing your code to:
>
> CREATE TABLE a (id TEXT);
> ALTER TABLE a ENABLE ROW LEVEL SECURITY;
> ALTER TABLE a FORCE ROW LEVEL SECURITY;
>
> CREATE TABLE b (id TEXT);
>
> CREATE POLICY a_select ON a FOR SELECT
> USING (EXISTS(
> select * from b where a.id = b.id
> ));
>
> CREATE POLICY a_insert ON a FOR INSERT
> WITH CHECK (true);
>
> CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
> BEGIN
> RAISE NOTICE USING MESSAGE = 'inside trigger handler';
> INSERT INTO b (id) VALUES (NEW.id);
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER reproTrigger BEFORE INSERT ON a
> FOR EACH ROW EXECUTE PROCEDURE reproHandler();
>
> Resulted in:
>
> test=# INSERT INTO a VALUES ('fails') returning id;
> NOTICE: inside trigger handler
> id
> ---
> fails
> (1 row)
>
> INSERT 0 1
> test=# select * from a;
> id
> ---
> fails
> (1 row)
>
>
> >
> > Carl Sverre
> >
> > http://www.carlsverre.com
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
> *[Charles] :* I did the same test with PG version 10 on Windows and PG
> 9.6.2 on Linux (RedHat) with exactly the same result.
>
> db=# INSERT INTO a VALUES ('fails') returning id;
> NOTICE:  inside trigger handler
>   id
> ---
>  fails
> (1 row)
>
> INSERT 0 1
> db=# select * from a;
>   id
> ---
>  fails
> (1 row)
>
> db=# select * from b;
>   id
> ---
>  fails
> (1 row)
>
> Regards
> Charles
>
>
> --
Carl Sverre

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-10-01 Thread Carl Sverre 
Thank you for the detailed report Charles. I think you may be missing the
“returning id” clause in the insert. Can you verify it works when you use
“returning id”? Thanks!
On Sun, Sep 30, 2018 at 7:57 PM Charles Clavadetscher (SwissPUG) <
clavadetsc...@swisspug.org> wrote:

> Hello
>
> On 30.09.2018 23:31:32, Adrian Klaver  wrote:
> On 9/30/18 1:13 PM, Carl Sverre wrote:
> > Thanks for the initial results. Can you check that you are not using
> > super permissions and are enabling row security when running the test?
> > Super ignores row security.
>
> Yeah, big oops on my part, I was running as superuser. Running as
> non-superuser resulted in the failure you see. I tried to get around
> this with no success. My suspicion is that the new row in b is not
> visible to the returning(SELECT) query in a until after the transaction
> completes. Someone with more knowledge on this then I will have to
> confirm/deny my suspicion.
>
>
> >
> > Also yes, I forgot to add the policy names, sorry about that.
> > On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG)
> > > wrote:
> >
> > Hello
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
> *[Charles] :* I also made the first test as super. However I still don't
> get any errors when executing the test query as non superuser.
>
> The user is not superuser:
>
> testuser@charles.localhost=> SELECT CURRENT_USER;
>  current_user
> --
>  testuser
> (1 row)
>
> testuser@charles.localhost=> \du testuser
>List of roles
>  Role name | Attributes | Member of
> ---++---
>  testuser  || {}
>
> The table privileges show that RLS is enabled and that testuser has
> SELECT and INSERT privilege on both tables. This is not related to RLS but
> simple precondition for the test:
>
> testuser@charles.localhost=> \d a
> Table "public.a"
>  Column | Type | Collation | Nullable | Default
> +--+---+--+-
>  id | text |   |  |
> Policies (forced row security enabled):
> POLICY "a_insert" FOR INSERT
>   WITH CHECK (true)
> POLICY "a_select" FOR SELECT
>   USING ((EXISTS ( SELECT b.id
>FROM b
>   WHERE (a.id = b.id
> Triggers:
> reprotrigger BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE
> reprohandler()
>
> testuser@charles.localhost=> \dp a
>  Access privileges
>  Schema | Name | Type  |Access privileges| Column privileges |
>   Policies
>
> +--+---+-+---+--
>  public | a| table | charles=arwdDxt/charles+|   |
> a_select (r):   +
> |  |   | testuser=ar/charles |   |
> (u): (EXISTS ( SELECT b.id+
> |  |   | |   |
>  FROM b   +
> |  |   | |   |
> WHERE (a.id = b.id))) +
> |  |   | |   |
> a_insert (a):   +
> |  |   | |   |
> (c): true
>
> testuser@charles.localhost=> \d b
> Table "public.b"
>  Column | Type | Collation | Nullable | Default
> +--+---+--+-
>  id | text |   |  |
>
> testuser@charles.localhost=> \dp b
>Access privileges
>  Schema | Name | Type  |Access privileges| Column privileges |
> Policies
>
> +--+---+-+---+--
>  public | b| table | charles=arwdDxt/charles+|   |
> |  |   | testuser=ar/charles |   |
>
> And now the test:
>
> testuser@charles.localhost=> SELECT * FROM a;
>  id
> 
> (0 rows)
>
> testuser@charles.localhost=> SELECT * FROM b;
>  id
> 
> (0 rows)
>
> testuser@charles.localhost=> INSERT INTO a VALUES ('fails');
> NOTICE:  inside trigger handler
> INSERT 0 1
> testuser@charles.localhost=> SELECT * FROM a;
>   id
> ---
>  fails
> (1 row)
>
> testuser@charles.localhost=> SELECT * FROM b;
>   id
> ---
>  fails
> (1 row)
>
> Version of PG:
> testuser@charles.localhost=> SELECT version();
>   version
> 
>  PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
> (1 row)
>
> Regards
> Charles
>
> --
Carl Sverre

Re: regarding bdr extension

2018-10-01 Thread Durgamahesh Manne 
On Fri, Sep 28, 2018 at 10:43 PM Adrian Klaver 
wrote:

> On 9/28/18 8:41 AM, Durgamahesh Manne wrote:
> > Hi
> >
> > This is regarding bdr extension issue. I got below error at the time i
> > have tried to create the bdr extention
> >
> >
> > ERROR: could not open extension control file
> > "opt/PostgreSQL/10/share/postgresql/extension/bdr.control": No such file
> > or directory
>
> OS and version?
>
> How are you installing BDR, from package or source?
>
> What was the exact command that led to the error?
>
> Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?
>
> >
> >
> >
> > Regards
> > Durgamahesh Manne
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



 Hi

OS and version?

DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"
NAME="Ubuntu"
VERSION="16.04.3 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.3 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/";
SUPPORT_URL="http://help.ubuntu.com/";
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/";
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial

database version is 10

2) How are you installing BDR, from package or source?

i am trying to install bdr by using postgres 10 one click installer

3) What was the exact command that led to the error?

 after  i have installed postgres 10  i ran CREATE EXTENSION bdr led to
error

4) Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?

  i ran CREATE EXTENSION bdr which led to below error

  > ERROR: could not open extension control file
> "opt/PostgreSQL/10/share/postgresql/extension/bdr.control": No such file
> or directory



   Regards

Durgamahesh Manne

libpq.dll question

2018-10-01 Thread hamann . w 


Hi,

admitting that windows is quite alien to me...
I have installed a postgresql server on linux and now want to allow windows 
clients access through a tcl gui.
Activestate tcl is installed, and when I try to
package require tdbc::postgresql
I get a complaint about libpq.dll.5 missing. The postgres install kit contains 
just a libpq.dll, and renaming it
does not seem to help

Best regards
Wolfgang Hamann

Re: libpq.dll question

2018-10-01 Thread Dmitry Igrishin 
пн, 1 окт. 2018 г. в 11:15, :
>
>
> Hi,
>
> admitting that windows is quite alien to me...
> I have installed a postgresql server on linux and now want to allow windows 
> clients access through a tcl gui.
> Activestate tcl is installed, and when I try to
> package require tdbc::postgresql
> I get a complaint about libpq.dll.5 missing. The postgres install kit 
> contains just a libpq.dll, and renaming it
> does not seem to help
While I have no idea what is "libpq.dll.5", but have you tryed to make
a symbolic link "libpq.dll.5" to "libpq.dll" with "mklink" command to
workaround?

backend_xmin in pg_stat_replication

2018-10-01 Thread Torsten Förtsch 
Hi,

if I understand it correctly, backend_xmin in pg_stat_replication is the
xmin that's reported back by hot_standby_feedback. Given there are no
long-running transactions on the replica, I presume that value should be
pretty close to the xmin field of any recent snapshots on the master. This
is true for all my databases but one:

select application_name,
   txid_snapshot_xmin(txid_current_snapshot()),
   backend_xmin::TEXT::BIGINT,

 txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
  from pg_stat_replication;

 application_name | txid_snapshot_xmin | backend_xmin |  ?column?
--++--+
 xx   | 6957042833 |   2662075435 | 4294967398

Over time, that backend_xmin is slowly advancing.

If I call txid_current_snapshot() in a new session on the replica, I get
reasonable numbers but the backend_xmin in pg_stat_activity is equally off.

select backend_xmin, txid_current_snapshot()
  from pg_stat_activity
 where backend_xmin is not null;

 backend_xmin | txid_current_snapshot
--+
   2662207433 | 6957174729:6957174729:

Is that expected behavior? Or is there anything wrong?

All other backends are idle and all but one pretty fresh. One has been
running for about 2 months with short-lasting transactions every now and
again.

Thanks,
Torsten

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-10-01 Thread Dean Rasheed 
The real issue here is to do with the visibility of the data inserted
by the trigger function from within the same command. In general, data
inserted by a command is not visible from within that same command.

The easiest way to see what's going on is with a simple example.
Consider the following (based on the original example, but without any
RLS):


DROP TABLE IF EXISTS a,b;

CREATE TABLE a (id text);
CREATE TABLE b (id text);

CREATE OR REPLACE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE USING MESSAGE = 'inside trigger handler';
INSERT INTO b (id) VALUES (NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

CREATE OR REPLACE FUNCTION check_b1(text) RETURNS boolean AS $$
BEGIN
  RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION check_b2(text) RETURNS boolean AS $$
BEGIN
  RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
END
$$ LANGUAGE plpgsql VOLATILE;

INSERT INTO a VALUES ('xxx')
  RETURNING id, check_b1(id), check_b2(id),
(EXISTS (SELECT * FROM b WHERE b.id = a.id));

NOTICE:  inside trigger handler
 id  | check_b1 | check_b2 | exists
-+--+--+
 xxx | f| t| f
(1 row)

INSERT 0 1


Notice that the functions check_b1() and check_b2() are identical,
except that check_b1() is declared STABLE and check_b2() is declared
VOLATILE, and that makes all the difference. Quoting from the
documentation for function volatility [1]:

For functions written in SQL or in any of the standard procedural
languages, there is a second important property determined by the
volatility category, namely the visibility of any data changes that
have been made by the SQL command that is calling the function. A
VOLATILE function will see such changes, a STABLE or IMMUTABLE
function will not.

[1] https://www.postgresql.org/docs/10/static/xfunc-volatility.html

Also notice that the inline EXISTS query behaves in the same way as
the STABLE function -- i.e., it does not see changes made in the
current query.

So returning to the RLS example, because the RLS SELECT policy is
defined using inline SQL, it cannot see the changes made by the
trigger. If you want to see such changes, you need to define a
VOLATILE function to do the RLS check.

Regards,
Dean

Re: libpq.dll question

2018-10-01 Thread Adrian Klaver 

On 10/1/18 1:14 AM, haman...@t-online.de wrote:


Hi,

admitting that windows is quite alien to me...
I have installed a postgresql server on linux and now want to allow windows 
clients access through a tcl gui.
Activestate tcl is installed, and when I try to


What version of Activestate tcl?


package require tdbc::postgresql


Where is tdbc::postgresql coming from?


I get a complaint about libpq.dll.5 missing. The postgres install kit contains 
just a libpq.dll, and renaming it


What is the Postgres install kit you refer to?


does not seem to help

Best regards
Wolfgang Hamann







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

Re: Why my query not using index to sort?

2018-10-01 Thread Adrian Klaver 

On 9/30/18 9:26 PM, Arup Rakshit wrote:

Hello Adrian,

The one I said in gist is different query, and the previous mail I 
posted another query because I was testing with different types of 
queries. So 1.5, 1.7 is not for this current one. My main point now I am 
trying to understand why it is not matching at all. The current query 
timing in psql and production log is very close, but not the explain output.


1) If you want to track down what is happening you will need to provide 
a consistent test case. Jumping from query to query, dataset to dataset, 
condition to condition is not conducive to coming up with an answer.


2) What is the answer you are looking for? Or to put it another way what 
is the problem you are trying to solve?


3) Taking 1) & 2) into account you need to do something along lines of:
a) Information about size of table.
b) A set query against said table.
c) Some indication of the problem the query is causing.



Regarding the file written time, I think it is not the time to write 
into the file. Because If I don’t write this to the file still it shows 
14ms + always. I used the file not the sql output so that I can paste 
here clean stuffs I want to show. If you want, I can show the output 
without writing it to the external file, and you will see the same time.





Well you are looking at two different times. The EXPLAIN ANALYZE is 
showing the time to execute the query. The \timing is including the time 
to display the output which is why is comparable to what you see in the 
log. To illustrate:


select count(*) from projection ;
 count
---
 28447
(1 row)

\timing
Timing is on.

 explain analyze select * from projection ;
   QUERY PLAN 



 Seq Scan on projection  (cost=0.00..751.47 rows=28447 width=109) 
(actual time=0.012..3.853 rows=28447 loops=1)

 Planning time: 0.066 ms
 Execution time: 5.381 ms
(3 rows)



explain analyze select * from projection where p_item_no = 2;
QUERY PLAN 


---
 Index Scan using pj_pno_idx on projection  (cost=0.29..21.93 rows=5 
width=109) (actual time=0.021..0.032 rows=10 loops=1)

   Index Cond: (p_item_no = 2)
 Planning time: 0.117 ms
 Execution time: 0.061 ms
(4 rows)

select * from projection ; --Returns 28447 rows
Time: 56.186 ms

select * from projection where p_item_no = 2; --Returns 10 rows
Time: 0.372 ms

Repeating it:

explain analyze select * from projection ;
   QUERY PLAN 



 Seq Scan on projection  (cost=0.00..751.47 rows=28447 width=109) 
(actual time=0.012..3.377 rows=28447 loops=1)

 Planning time: 0.056 ms
 Execution time: 4.759 ms
(3 rows)


explain analyze select * from projection where p_item_no = 2;
QUERY PLAN 


---
 Index Scan using pj_pno_idx on projection  (cost=0.29..21.93 rows=5 
width=109) (actual time=0.021..0.031 rows=10 loops=1)

   Index Cond: (p_item_no = 2)
 Planning time: 0.112 ms
 Execution time: 0.059 ms
(4 rows)


select * from projection ;
Time: 56.548 ms

select * from projection where p_item_no = 2;
Time: 0.463 ms





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

Re: libpq.dll question

2018-10-01 Thread Adrian Klaver 

On 10/1/18 6:36 AM, wolfg...@alle-noten.de wrote:

Please reply to list also.
Ccing list.
I am getting ready to head out the door which is why I am returning this 
to list for other eyes to see.




On 10/1/18 1:14 AM, haman...@t-online.de wrote:


Hi,

admitting that windows is quite alien to me...
I have installed a postgresql server on linux and now want to allow windows 
clients access through a tcl gui.
Activestate tcl is installed, and when I try to


What version of Activestate tcl?


Hi, I am not at the machine right now; it is some Tcl8.6 (installed during last 
year)



package require tdbc::postgresql


Where is tdbc::postgresql coming from?

this is available as part of activestate (not sure whether it is called 
tdbc:postgres or tdbc:postgresql)



I get a complaint about libpq.dll.5 missing. The postgres install kit contains 
just a libpq.dll, and renaming it


What is the Postgres install kit you refer to?

I downloaded the file from https://www.postgresql.org/download/windows/
and extracted the dll file





Best regards
Wolfgang Hamann




does not seem to help

Best regards
Wolfgang Hamann







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






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

Re: regarding bdr extension

2018-10-01 Thread Adrian Klaver 

On 10/1/18 1:08 AM, Durgamahesh Manne wrote:



On Fri, Sep 28, 2018 at 10:43 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 9/28/18 8:41 AM, Durgamahesh Manne wrote:
 > Hi
 >
 > This is regarding bdr extension issue. I got below error at the
time i
 > have tried to create the bdr extention
 >
 >
 > ERROR: could not open extension control file
 > "opt/PostgreSQL/10/share/postgresql/extension/bdr.control": No
such file
 > or directory

OS and version?

How are you installing BDR, from package or source?

What was the exact command that led to the error?

Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?

 >
 >
 >
 > Regards
 > Durgamahesh Manne


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



  Hi

     OS and version?

DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"
NAME="Ubuntu"
VERSION="16.04.3 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.3 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/";
SUPPORT_URL="http://help.ubuntu.com/";
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/";
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial

database version is 10

2) How are you installing BDR, from package or source?

i am trying to install bdr by using postgres 10 one click installer


This one?:

https://www.postgresql.org/download/linux/

Since you are on Ubuntu why not use the PGDG repo?:

https://www.postgresql.org/download/linux/ubuntu/



3) What was the exact command that led to the error?

  after  i have installed postgres 10  i ran CREATE EXTENSION bdr led to 
error


4) Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?


Understood, but the question remains does that file exist in the 
location specified above?


If it does then there is possibly a permissions problem. If it does not 
then either the extension was installed somewhere else or it was not 
installed at all.




   i ran CREATE EXTENSION bdr which led to below error


ERROR: could not open extension control file
"opt/PostgreSQL/10/share/postgresql/extension/bdr.control": No such file
or directory




    Regards

Durgamahesh Manne




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

Re: backend_xmin in pg_stat_replication

2018-10-01 Thread Andres Freund 
Hi,

On 2018-10-01 12:20:26 +0200, Torsten Förtsch wrote:
> if I understand it correctly, backend_xmin in pg_stat_replication is the
> xmin that's reported back by hot_standby_feedback. Given there are no
> long-running transactions on the replica, I presume that value should be
> pretty close to the xmin field of any recent snapshots on the master. This
> is true for all my databases but one:
> 
> select application_name,
>txid_snapshot_xmin(txid_current_snapshot()),
>backend_xmin::TEXT::BIGINT,
> 
>  txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
>   from pg_stat_replication;
> 
>  application_name | txid_snapshot_xmin | backend_xmin |  ?column?
> --++--+
>  xx   | 6 957 042 833 |   2 662 075 435 | 4 294 967 398

I don't think the calculation you're doing here is correct.
backend_xmin is an xid (max 2^32-1), whereas txid_snapshot_xmin returns
an xid *with* epoch (max 2^64-1). What you're measuring here is simply
the fact that the xid counter has wrapped around.

Greetings,

Andres Freund

PostgreSQL FIPS 140-2 on Window

2018-10-01 Thread Bradley May 
I understand the PostgreSQL 9.x installed on a RHEL distribution can be 
configured and supports FIPS 140-2 when using and properly configuring OpenSSL 
FIPS. My question is can the same be accomplished with a Windows installation, 
as easily or similar to the RHEL installation/configuration procedures?

Apologies for such an abstract questions, but I remember reading somewhere that 
PostgreSQL 9.x  when installed on Windows does not support FIPS 140-2 without 
installing a more commercial product that has performed the additional 
compilation requirements.

Respectfully,
brad

Re: regarding bdr extension

2018-10-01 Thread Durgamahesh Manne 
On Mon, Oct 1, 2018 at 7:34 PM Adrian Klaver 
wrote:

> On 10/1/18 1:08 AM, Durgamahesh Manne wrote:
> >
> >
> > On Fri, Sep 28, 2018 at 10:43 PM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 9/28/18 8:41 AM, Durgamahesh Manne wrote:
> >  > Hi
> >  >
> >  > This is regarding bdr extension issue. I got below error at the
> > time i
> >  > have tried to create the bdr extention
> >  >
> >  >
> >  > ERROR: could not open extension control file
> >  > "opt/PostgreSQL/10/share/postgresql/extension/bdr.control": No
> > such file
> >  > or directory
> >
> > OS and version?
> >
> > How are you installing BDR, from package or source?
> >
> > What was the exact command that led to the error?
> >
> > Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?
> >
> >  >
> >  >
> >  >
> >  > Regards
> >  > Durgamahesh Manne
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
> >
> >
> >   Hi
> >
> >  OS and version?
> >
> > DISTRIB_ID=Ubuntu
> > DISTRIB_RELEASE=16.04
> > DISTRIB_CODENAME=xenial
> > DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"
> > NAME="Ubuntu"
> > VERSION="16.04.3 LTS (Xenial Xerus)"
> > ID=ubuntu
> > ID_LIKE=debian
> > PRETTY_NAME="Ubuntu 16.04.3 LTS"
> > VERSION_ID="16.04"
> > HOME_URL="http://www.ubuntu.com/";
> > SUPPORT_URL="http://help.ubuntu.com/";
> > BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/";
> > VERSION_CODENAME=xenial
> > UBUNTU_CODENAME=xenial
> >
> > database version is 10
> >
> > 2) How are you installing BDR, from package or source?
> >
> > i am trying to install bdr by using postgres 10 one click installer
>
> This one?:
>
> https://www.postgresql.org/download/linux/
>
> Since you are on Ubuntu why not use the PGDG repo?:
>
> https://www.postgresql.org/download/linux/ubuntu/
>
> >
> > 3) What was the exact command that led to the error?
> >
> >   after  i have installed postgres 10  i ran CREATE EXTENSION bdr led to
> > error
> >
> > 4) Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?
>
> Understood, but the question remains does that file exist in the
> location specified above?
>
> If it does then there is possibly a permissions problem. If it does not
> then either the extension was installed somewhere else or it was not
> installed at all.
>
> >
> >i ran CREATE EXTENSION bdr which led to below error
> >
> >> ERROR: could not open extension control file
> >opt/PostgreSQL/10/share/postgresql/extension/bdr.control": No such file

>> or directory> "
> >
> >
> >
> > Regards
> >
> > Durgamahesh Manne
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



 Hi  sir

* Since you are on Ubuntu why not use the PGDG repo?:

 in my company env  i am using pg installer to manage the postgres servers

does this pgdg repository useful for configuration of bdr v3 ?

If yes then i can use it for bdr

please let me know some information about pgdg repository in detail

* Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?

   bdr.control  file not available


Regards

Durgamahesh Manne

Re: PostgreSQL FIPS 140-2 on Window

2018-10-01 Thread Tom Lane 
Bradley May  writes:
> I understand the PostgreSQL 9.x installed on a RHEL distribution can be 
> configured and supports FIPS 140-2 when using and properly configuring 
> OpenSSL FIPS. My question is can the same be accomplished with a Windows 
> installation, as easily or similar to the RHEL installation/configuration 
> procedures?

No.  PG doesn't really have any specific support for the FIPS
requirements.  From our perspective that's implemented by behavioral
changes in libc and openssl.  Perhaps you can find FIPS-ified versions
of those libraries for Windows, but I don't know where to look.

regards, tom lane

Re: backend_xmin in pg_stat_replication

2018-10-01 Thread Torsten Förtsch 
Thanks a lot! So, the correct calculation is like this:

select application_name,
   txid_snapshot_xmin(txid_current_snapshot()),
   backend_xmin::TEXT::BIGINT,

 
(txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT)%(2^32)::BIGINT
  from pg_stat_replication;

 application_name | txid_snapshot_xmin | backend_xmin | ?column?
--++--+--
 xx   | 6960964080 |   2665996642 |  142


That makes more sense.

On Mon, Oct 1, 2018 at 5:32 PM Andres Freund  wrote:

> Hi,
>
> On 2018-10-01 12:20:26 +0200, Torsten Förtsch wrote:
> > if I understand it correctly, backend_xmin in pg_stat_replication is the
> > xmin that's reported back by hot_standby_feedback. Given there are no
> > long-running transactions on the replica, I presume that value should be
> > pretty close to the xmin field of any recent snapshots on the master.
> This
> > is true for all my databases but one:
> >
> > select application_name,
> >txid_snapshot_xmin(txid_current_snapshot()),
> >backend_xmin::TEXT::BIGINT,
> >
> >  txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
> >   from pg_stat_replication;
> >
> >  application_name | txid_snapshot_xmin | backend_xmin |  ?column?
> > --++--+
> >  xx   | 6 957 042 833 |   2 662 075 435 | 4 294 967
> 398
>
> I don't think the calculation you're doing here is correct.
> backend_xmin is an xid (max 2^32-1), whereas txid_snapshot_xmin returns
> an xid *with* epoch (max 2^64-1). What you're measuring here is simply
> the fact that the xid counter has wrapped around.
>
> Greetings,
>
> Andres Freund
>

Re: PostgreSQL FIPS 140-2 on Window

2018-10-01 Thread Joe Conway 
On 10/01/2018 11:44 AM, Bradley May wrote:
> I understand the PostgreSQL 9.x installed on a RHEL distribution can
> be configured and supports FIPS 140-2 when using and properly
> configuring OpenSSL FIPS. My question is can the same be accomplished
> with a Windows installation, as easily or similar to the RHEL
> installation/configuration procedures?
> 
> Apologies for such an abstract questions, but I remember reading
> somewhere that PostgreSQL 9.x  when installed on Windows does not
> support FIPS 140-2 without installing a more commercial product that
> has performed the additional compilation requirements.

As I understand it, FIPS 140-2 support is both "mechanical" (as in your
application will use only FIPS 140-2 approved algorithms and openssl
will be in "FIPS mode" if asked) as well as "compliance" (as in using
software that is actually certified to be FIPS 140-2 compliant).

While without a support subscription you can get "mechanical" FIPS 140-2
support with properly patched OpenSSL library (e.g. using CentOS
configured for FIPS 140-2 system-wide), you will not have FIPS 140-2
"compliance" unless you pay for support from a company that maintains
the certification (e.g. Red Hat, Ubuntu, or SUSE).

The challenge on Windows is to find an SSL library that:

1. Works with Postgres (i.e. openssl or something compatible)
2. Enables Postgres to have mechanical compliance (i.e. works
   system wide in a way that is transparent to Postgres)
3. Is backed by a company that has FIPS 140-2 certification for it

The only one that I have run across that appears to meet all three of
these on Windows is wolfSSL:

  https://www.wolfssl.com/products/wolfssl/

Note that I have no affiliation with them, nor have I actually tried the
product. It claims to have an "OpenSSL Compatibility Layer", so perhaps
it might work for you. If you try it, I'd love to hear back how it goes :-)

HTH,

Joe

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



signature.asc
Description: OpenPGP digital signature

Price Request MXO-PPQ-101164

2018-10-01 Thread Pichardo, Ari 
Hello team,

My name is Ari and I work for SoftwareOne, an international software consulting 
and reseller company.

We have an end user interested in:


* 1 2ndQPostgres Platinium

For their work environment in Mexico.

Do you work with resellers?

Can we get a price quote?

Best regards,

[cid:image001.png@01D28115.EB3808A0]
[cid:image002.gif@01D28115.EB3808A0] 
 
[cid:image003.gif@01D28115.EB3808A0] [cid:image004.gif@01D28115.EB3808A0] 
 [cid:image005.gif@01D28115.EB3808A0] 
[cid:image006.gif@01D28115.EB3808A0]  
[cid:image007.png@01D28115.EB3808A0]

Ari Pichardo | Procurement Specialist |
Global Service Delivery Center
Phone: +1 214 666 7587
ari.picha...@softwareone.com|www.softwareone.com

CREATE TABLE AS SELECT hangs

2018-10-01 Thread derek 

Hello,

I am trying to populate/create a database with CREATE TABLE AS SELECT 
like so:


   CREATE TABLE test_table AS
   SELECT row_number() over() as gid, cells.geom
   FROM test_geom_function(1,2,3) AS cells;

This works on one database instance, but not on another. On the database 
it doesn't work on it seems to hang on executing and when I cancel it I get


   ERROR:  canceling statement due to user request
   CONTEXT:  while inserting index tuple (6,13) in relation
   "pg_type_typname_nsp_index"
   SQL state: 57014

Any help/suggestions would be greatly appreciated.  I am running 
PostgreSQL 9.6 with PostGIS also for some spatial functionality.


Thanks,

Derek

Re: Price Request MXO-PPQ-101164

2018-10-01 Thread Andreas Kretschmer 




Am 01.10.2018 um 20:02 schrieb Pichardo, Ari:


Hello team,

My name is Ari and I work for SoftwareOne, an international software 
consulting and reseller company.


We have an end user interested in:

·1 2ndQPostgres Platinium

For their work environment in Mexico.

Do you work with resellers?

Can we get a price quote?





Hi,

i'm working fpr 2ndQ and i will forward your mail to our headquarter.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: CREATE TABLE AS SELECT hangs

2018-10-01 Thread Tom Lane 
derek  writes:
> I am trying to populate/create a database with CREATE TABLE AS SELECT 
> like so:

> CREATE TABLE test_table AS
> SELECT row_number() over() as gid, cells.geom
> FROM test_geom_function(1,2,3) AS cells;

> This works on one database instance, but not on another. On the database 
> it doesn't work on it seems to hang on executing and when I cancel it I get

> ERROR:  canceling statement due to user request
> CONTEXT:  while inserting index tuple (6,13) in relation
> "pg_type_typname_nsp_index"
> SQL state: 57014

Hmm (pokes around) ... That error context message seems to only be
possible if we were blocked waiting for some other transaction.
I theorize that you have an uncommitted transaction someplace that
has created the same table name.  Cancelling it would fix things.

regards, tom lane

Postgres 11 procedures and result sets

2018-10-01 Thread Jan Kohnert 
Hello List,

I have a question regarding the new stored procedures in Postgres 11 (I tested 
beta4):

I'd like to know if it is somehow possible to get a (or possibly more) result 
set from selects within the SP, as it is possible in MariaDB, MySQL, or SQL 
Server. 

What I found until now is to use inout-Parms. Then I have to define all 
columns in the select as inout-Params, but then I get only one line back, 
regardless how many lines >0 have been selected.

I could also define an inout refcursor param, and parse that one, but that 
would make the new SP identical to the well known functions returning a 
refcursor. And when I use that approach, I have an additional step in Qt-Code 
for example, since I have to exec the SP, then parse to the refcursor result, 
exec the fetch and then parse the cursor output I'm interested in.

Did I miss something? 

Thanks in advance!

-- 
MfG Jan

Re: Postgres 11 procedures and result sets

2018-10-01 Thread Tom Lane 
Jan Kohnert  writes:
> I have a question regarding the new stored procedures in Postgres 11 (I 
> tested 
> beta4):
> I'd like to know if it is somehow possible to get a (or possibly more) result 
> set from selects within the SP, as it is possible in MariaDB, MySQL, or SQL 
> Server. 

Not there as of v11, other than the refcursor approach you already know
about.  We hope to have something nicer worked out for v12.  There
are a lot of compatibility issues to sort through :-(

regards, tom lane

Postgres 11, partitioning with a custom hash function

2018-10-01 Thread Harry B 
Hi,

I am interested in trying the hash partitioning method now available in 11
(trying the beta 4). However, I have the data already hashed at the
application level across multiple postgres instances. If possible, I would
like to keep these two hashing methods same.  This would enable me to move
a single partition (considering we have attach/detach methods available)
from one instance to another and have queries work seamlessly.

The application can control what data/query is sent to each instance - the
only thing making this setup impossible is (a) the built-in HASH function
not available/accessible to me outside of pg context, say, as a C library.
and (b) I don't know how to sub a known hash function (say, murmur, xxhash,
siphash) instead of the builtin hash function. I am not particularly
insisting on any particular hash function, except for it to available
outside of postgres (say as a C or Go library).

Based on a discussion in the IRC channel, I was told I could try creating a
custom hash function (postgres extension) and use it in RANGE or LIST
partitioning with that expression.

I have the following code installed as a postgres extension
http://dpaste.com/2594KWM, takes an implementation of xxhash.c and sticks
it in as a postgres extension †
~/tmp/pge$ gcc -fPIC -I/usr/include/postgresql/11/server -c pge.c && gcc
-shared -o pge.so pge.o

Problem is that with this setup, partitioning of the writes/inserts work
fine, but I don't see pg excluding the unnecessary partitions on
read/queries

http://dpaste.com/1C0XY3M

This setup based on expression also has other issues - I can't mark k as a
primary key or have a unique key on that column. If it is declared as a
hash partition, I can have those.

This question may be related to this thread as well
https://www.postgresql-archive.org/Hash-Functions-td5961137.html

†  surprisingly, this function works even if I don't compile in xxhash.c/o
into the .so - that is yet another side mystery to figure out. It is like
the symbol XXH64 is already available dynamically. I did have plpython
installed at some point. but this is a separate issue.

-- 
Harry