Arup Rakshit | 24 May 12:26 2015
Picon

How to skip duplicate records while copying from CSV to table in Postgresql using "COPY"

Hi,

I am copying the data from a CSV file to a Table using "COPY" command. But one thing that I got stuck, is how to
skip duplicate records while copying from CSV to tables. By looking at the documentation, it seems,
Postgresql don't have any inbuilt too to handle this with "copy" command. By doing Google I got below 1 idea
to use temp table.

http://stackoverflow.com/questions/13947327/to-ignore-duplicate-keys-during-copy-from-in-postgresql

I am also thinking what if I let the records get inserted, and then delete the duplicate records from table as
this post suggested - http://www.postgresql.org/message-id/37013500.DFF0A64A <at> manhattanproject.com.

Both of the solution looks like doing double work. But I am not sure which is the best solution here. Can
anybody suggest which approach should I adopt ? Or if any better ideas you guys have on this task, please share.

Thanks in advance!

-- 
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as
cleverly as possible, you are, by definition, not smart enough to debug it.

--Brian Kernighan

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
(Continue reading)

Daniel Begin | 24 May 03:46 2015
Picon

Re: FW: Constraint exclusion in partitions

Oops, I was responding to the email below from melvin6925

 

 

From: pgsql-general-owner <at> postgresql.org [mailto:pgsql-general-owner <at> postgresql.org] On Behalf Of David G. Johnston
Sent: May-23-15 19:32
To: Daniel Begin
Cc: melvin6925; pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] [NOVICE] Constraint exclusion in partitions

 

On Saturday, May 23, 2015, Daniel Begin <jfd553 <at> hotmail.com> wrote:

I am working with postgresql 9.3 and I understand from the documentation that constraint_exclusion is set to “partition” by default. Looking at my postgres.conf file, the concerned line is “#constraint_exclusion = partition”. 

Furthermore, the execution plan shows that constraint_exclusion was used at least for constant id and range of ids

What is your question/concern? 

 

 

Did you remember to set  constraint_exclusion = on and reload the .conf ?

 

 

 

From: pgsql-general-owner <at> postgresql.org [mailto:pgsql-general-owner <at> postgresql.org] On Behalf Of melvin6925
Sent: May-23-15 15:15
To: Daniel Begin; pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] FW: Constraint exclusion in partitions

 

Did you remember to set  constraint_exclusion = on and reload the .conf ?

 

 

 

 

Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone

-------- Original message --------
From: Daniel Begin <jfd553 <at> hotmail.com>
Date: 05/23/2015 14:37 (GMT-05:00)
To: pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] FW: Constraint exclusion in partitions

Following Francisco suggestion, I was able to do some tests earlier this morning when the partitioning process completed and all the resulting tables analyzed.

Here is what I got on both the original table and its partitioned counterpart while running the same queries. I tested them only for a couple of values but in summary...

Using a constant id:
All the queries I tried took longer on the partitioned table! I got similar results for multiple records using IN (id value1, id value2 ...)

Using a range of ids:
Surprisingly again, all the queries I tried took longer on the partitioned table!

Using a list of ids from a select clause:
More surprisingly, the queries I tried took less time on the partitioned table at least when using the primary key. Using an indexed field took so long compared to the old table that I cancelled the execution for the new one!

Guess what, I will get back to my old fat table unless someone tells me I missed something obvious!
Daniel

Note: Tables/indexes description, queries and execution plans are below.






Tables/indexes description ----------------------------------------------------------------------------------
The original table has 3870130000 records. Primary key/index on each partition queries are
ALTER TABLE oldtable ADD CONSTRAINT oldtable_idversion_pk PRIMARY KEY (id, version);
CREATE INDEX oldtable_groupid_idx ON oldtable USING btree (group_id);

The partitioned table has 3870130000 records distributed over 87 partitions. Primary key/index on each partition queries are
ALTER TABLE newtable_xx ADD CONSTRAINT newtablexx_idversion_pk PRIMARY KEY (id, version);
CREATE INDEX newtablexx_groupid_idx ON newtable_xx USING btree (group_id);
Where xx is the partition's number suffix

constant id -------------------------------------------------------------------------------------------------------
select * from oldtable where id=123456789;
"Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..4437.15 rows=1682 width=66)"
"  Index Cond: (id = 123456789::bigint)"
--Total query runtime: 62 ms. 1 rows retrieved


select * from newtable where id=123456789;
"Append  (cost=0.00..20.19 rows=5 width=66)"
"  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: (id = 123456789::bigint)"
"  ->  Index Scan using newtable72_idversion_pk on newtable_72  (cost=0.56..20.19 rows=4 width=66)"
"        Index Cond: (id = 123456789::bigint)"
--Total query runtime: 156 ms. 1 rows retrieved

I got similar results for multiple records...
select * from oldtable where id IN(10000000,1000000000,2000000000,3000000000);
"Index Scan using oldtable_idversion_pk on oldtable  (cost=0.71..17739.18 rows=6726 width=66)"
"  Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
--Total query runtime: 187 ms. 4 rows retrieved

select * from newtable where id IN(10000000,1000000000,2000000000,3000000000);
"Append  (cost=0.00..933.40 rows=223 width=66)"
"  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
"  ->  Index Scan using newtable01_idversion_pk on newtable_01  (cost=0.57..622.78 rows=156 width=66)"
"        Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
...
"  ->  Index Scan using newtable85_idversion_pk on newtable_85  (cost=0.57..53.37 rows=9 width=66)"
"        Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
--Total query runtime: 421 ms. 4 rows retrieved


range of ids -------------------------------------------------------------------------------------------------------
select * from oldtable where id between 1522999949 and 1523000049;
"Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..383.51 rows=144 width=66)"
"  Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
Total query runtime: 47 ms. 53 rows retrieved.

select * from newtable where id between 1522999949 and 1523000049;
"Append  (cost=0.00..408.16 rows=104 width=66)"
"  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: ((id >= 1522999949) AND (id <= 1523000049))"
"  ->  Index Scan using newtable51_idversion_pk on newtable_51  (cost=0.56..183.52 rows=46 width=66)"
"        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
"  ->  Index Scan using newtable52_idversion_pk on newtable_52  (cost=0.56..224.64 rows=57 width=66)"
"        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
Total query runtime: 78 ms. 53 rows retrieved.


list of ids from a select clause -------------------------------------------------------------------------------------------------------
--Subset provides 4 ids similar but not identical to the previous query
select * from oldtable where id IN (select * from subset);
"Nested Loop  (cost=37.45..886298.00 rows=2028512050 width=66)"
"  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
"  ->  Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..4414.37 rows=1693 width=66)"
"        Index Cond: (id = subset.id)"
Total query runtime: 171 ms. 4 rows retrieved.

select * from newtable where id IN (select * from subset)
"Nested Loop  (cost=36.75..1407672.76 rows=1935067087 width=66)"
"  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
"  ->  Append  (cost=0.00..7020.68 rows=1749 width=66)"
"        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"              Filter: (subset.id = id)"
"        ->  Index Scan using newtable01_idversion_pk on newtable_01  (cost=0.56..151.97 rows=39 width=66)"
"              Index Cond: (id = subset.id)"
...
"        ->  Index Scan using newtable86_idversion_pk on newtable_86  (cost=0.56..12.42 rows=2 width=66)"
"              Index Cond: (id = subset.id)"
Total query runtime: 140 ms. 4 rows retrieved.


Using an index, not the primary key ------------------------------------------------------------------------------
--Subset provides 58 group_id pointing to 5978 records in the concerned tables
select * from oldtable where group_id IN (select * from subset)
"Nested Loop  (cost=37.33..21575715.89 rows=2028512050 width=66)"
"  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
"  ->  Index Scan using oldtable_groupid_idx on oldtable  (cost=0.58..107364.99 rows=51340 width=66)"
"        Index Cond: (group_id = subset.id)"
Total query runtime: 3986 ms. 5978 rows retrieved.


select * from newtable where group_id IN (select * from subset)
"Hash Join  (cost=41.25..138092255.85 rows=1935067087 width=66)"
"  Hash Cond: (newtable.group_id = subset.id)"
"  ->  Append  (cost=0.00..84877869.72 rows=3870134173 width=66)"
"        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        ->  Seq Scan on newtable_01  (cost=0.00..946235.96 rows=46526896 width=66)"
...
"        ->  Seq Scan on newtable_86  (cost=0.00..986527.64 rows=44269664 width=66)"
"  ->  Hash  (cost=38.75..38.75 rows=200 width=8)"
"        ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"              ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
Execution Cancelled after 766702 ms !

I tried the same with "SET enable_seqscan = OFF" and got an index scan of all tables;



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

Marcos Ortiz | 24 May 00:27 2015
Picon

Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3

Regards to all the list.
First all the info about the system:

O.S: CentOS 7 64 bits
PostgreSQL version:

SELECT version();
version
--------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.2.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 
4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

Neo4j version: 2.1.M
Py2neo version: 2.0.8
Python version:
python
Python 2.7.5 (default, Jun 17 2014, 18:11:42)
[GCC 4.8.2 20140120 (Red Hat 4.8.2-16)] on linux2

Now the problem:

We are working here to integrate PostgreSQL with Neo4j through PL/Python 
using the py2neo module for it, and when we want to send sentences to 
Neo4j using port 7474, the executed code raises a SocketError [Errno 13] 
Permission denied.

I tested the same code in a normal python script outside of PostgreSQL, 
and it works well, but the problem is when I use the code inside 
PostgreSQL with PL/Python.
This is the code of a simple function to search a node in the Neo4j graph:

CREATE OR REPLACE FUNCTION search_professor_node(nombre_prof text)
   RETURNS text AS
$BODY$
from py2neo import Graph
from py2neo.cypher import CypherTransaction

graph = Graph("http://neo4j:neo4j <at> 10.8.45.136:37474/db/data")
tx = graph.cypher.begin()
statement = "MATCH (pf:Professor) WHERE pf.nombre = {name} RETURN pf;" ,
tx.append(statement, parameters={'name': nombre_prof})
professor= tx.commit()
$BODY$
   LANGUAGE plpythonu VOLATILE
   COST 100;

and it can be used:
SELECT search_professor_node('Max');

The completed error is:

********** Error **********

ERROR: py2neo.packages.httpstream.http.SocketError: Permission denied
SQL state: XX000
Context: Traceback (most recent call last):
   PL/Python function "search_professor_node", line 6, in <module>
     tx = graph.cypher.begin()
   PL/Python function "search_professor_node", line 666, in cypher
   PL/Python function "search_professor_node", line 212, in metadata
   PL/Python function "search_professor_node", line 257, in get
   PL/Python function "search_professor_node", line 965, in get
   PL/Python function "search_professor_node", line 942, in __get_or_head
   PL/Python function "search_professor_node", line 432, in submit
   PL/Python function "search_professor_node", line 361, in submit
PL/Python function "search_professor_node"

Thanks a lot for your time, and I hope to find a suitable solution for it.

--

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

Daniel Begin | 24 May 00:16 2015
Picon

Re: FW: Constraint exclusion in partitions

Hello Bill, 
You wrote that my testing methodology is flawed - I hope you are right!  

However, I am a bit confused about your comments. Yes, I did edited the name
of the tables for clarity but if I miss the point I, I will do it again as I
am writing without modifying anything. Here is the procedure I follow and
results...

I use pgadmin_III sql window. I write the following query (I have changed
the id to make sure it does not use previous results still in memory)...

Select * from nodes where id=345678912; -- nodes is the real partitioned
table name

Now I select "explain query" from the menu and I get the following result...
"Append  (cost=0.00..384.08 rows=99 width=66)"
"  ->  Seq Scan on nodes  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: (id = 345678912)"
"  ->  Index Scan using nodes19_idversion_pk on nodes_19  (cost=0.56..384.08
rows=98 width=66)"
"        Index Cond: (id = 345678912)"

Now, I select "run" and I get one record as a result and the following
message in history tab...
-- Executing query:
Select * from nodes where id=345678912; 
Total query runtime: 62 ms.
1 row retrieved.

Now, if I use the same query on the original table using the same procedure,
here is what I get...
Select * from old_nodes where id=345678912; -- old_nodes is the real
original table name

Explain gives me the following
"Index Scan using nodes_idversion_pk on old_nodes  (cost=0.70..4437.15
rows=1682 width=66)"
"  Index Cond: (id = 345678912)"

Running the query gives me the same record with the following message in
history tab...
-- Executing query:
select * from old_nodes where id=345678912; 
Total query runtime: 62 ms.
1 row retrieved.

This time, the history tab shows that both took the same time to run (an
improvement!?)
Let's try this one using the same procedure...

Select * from old_nodes where id IN
(10050000,1000050000,2000050000,3000050000)
"Index Scan using nodes_idversion_pk on old_nodes  (cost=0.71..17739.18
rows=6726 width=66)"
"  Index Cond: (id = ANY
('{10050000,1000050000,2000050000,3000050000}'::bigint[]))"
-- Executing query:
Select * from old_nodes where id IN
(10050000,1000050000,2000050000,3000050000)
Total query runtime: 171 ms.
5 rows retrieved.

Select * from nodes where id IN (10050000,1000050000,2000050000,3000050000)
"Append  (cost=0.00..933.40 rows=223 width=66)"
"  ->  Seq Scan on nodes  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: (id = ANY
('{10050000,1000050000,2000050000,3000050000}'::bigint[]))"
"  ->  Index Scan using nodes01_idversion_pk on nodes_01  (cost=0.57..622.78
rows=156 width=66)"
"        Index Cond: (id = ANY
('{10050000,1000050000,2000050000,3000050000}'::bigint[]))"
"  ->  Index Scan using nodes38_idversion_pk on nodes_38  (cost=0.57..138.25
rows=31 width=66)"
"        Index Cond: (id = ANY
('{10050000,1000050000,2000050000,3000050000}'::bigint[]))"
"  ->  Index Scan using nodes63_idversion_pk on nodes_63  (cost=0.57..119.01
rows=26 width=66)"
"        Index Cond: (id = ANY
('{10050000,1000050000,2000050000,3000050000}'::bigint[]))"
"  ->  Index Scan using nodes85_idversion_pk on nodes_85  (cost=0.57..53.37
rows=9 width=66)"
"        Index Cond: (id = ANY
('{10050000,1000050000,2000050000,3000050000}'::bigint[]))"
-- Executing query:
Select * from nodes where id IN (10050000,1000050000,2000050000,3000050000)
Total query runtime: 140 ms.
5 rows retrieved.

This time the history tab shows that the query was shorter to run on
partitioned table (a real improvement!?)

I know, this is different from what I referred to in my original email (at
least both shows similar running time) but I swear, I did not change the
times when editing table names!-)

Do you see any glitch/flaw in the procedure I am using?
Someone has an idea about the problem behind this unexpected behavior? 

I really need to get much faster results with my queries on this large table
and partitioning was my last option...

Best regards,
Daniel

-----Original Message-----
From: pgsql-general-owner <at> postgresql.org
[mailto:pgsql-general-owner <at> postgresql.org] On Behalf Of Bill Moran
Sent: May-23-15 15:23
To: Daniel Begin
Cc: pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] FW: Constraint exclusion in partitions

A large portion of why you describe below is the exact opposite of my own
testing (unfortunately, I don't have the actual test results any more
because I did the tests for a former employer).

In my tests, single lookups against the same column being used to partition
improved performance in direct proportion to the number of partitions. I.e.
if the tables are partitioned on id, and the lookup is for id, and the table
has 10 partitions, the query is 10x faster on the partitioned version than
the non-partitioned verison.

Queries against indexes not partitioned were slightly slower in my tests,
but nowhere near the degree that you're showing below.

I can't help but think that your testing methodology is flawed, but since
you're not showing us what you actually did, it's difficult to be sure. See
below for some specifics on what I'm concerned that you might be doing wrong
...

On Sat, 23 May 2015 14:37:25 -0400
Daniel Begin <jfd553 <at> hotmail.com> wrote:

> Following Francisco suggestion, I was able to do some tests earlier this
morning when the partitioning process completed and all the resulting tables
analyzed.
> 
> Here is what I got on both the original table and its partitioned
counterpart while running the same queries. I tested them only for a couple
of values but in summary...
> 
> Using a constant id: 
> All the queries I tried took longer on the partitioned table! I got 
> similar results for multiple records using IN (id value1, id value2 
> ...)
> 
> Using a range of ids: 
> Surprisingly again, all the queries I tried took longer on the partitioned
table!
> 
> Using a list of ids from a select clause: 
> More surprisingly, the queries I tried took less time on the partitioned
table at least when using the primary key. Using an indexed field took so
long compared to the old table that I cancelled the execution for the new
one!
> 
> Guess what, I will get back to my old fat table unless someone tells me I
missed something obvious!
> Daniel
> 
> Note: Tables/indexes description, queries and execution plans are below.
> 
> 
> 
> 
> 
> 
> Tables/indexes description 
> ----------------------------------------------------------------------
> ------------ The original table has 3870130000 records. Primary 
> key/index on each partition queries are ALTER TABLE oldtable ADD 
> CONSTRAINT oldtable_idversion_pk PRIMARY KEY (id, version); CREATE 
> INDEX oldtable_groupid_idx ON oldtable USING btree (group_id);
> 
> The partitioned table has 3870130000 records distributed over 87 
> partitions. Primary key/index on each partition queries are ALTER 
> TABLE newtable_xx ADD CONSTRAINT newtablexx_idversion_pk PRIMARY KEY 
> (id, version); CREATE INDEX newtablexx_groupid_idx ON newtable_xx 
> USING btree (group_id); Where xx is the partition's number suffix
> 
> constant id 
> ----------------------------------------------------------------------
> --------------------------------- select * from oldtable where 
> id=123456789; "Index Scan using oldtable_idversion_pk on oldtable  
> (cost=0.70..4437.15 rows=1682 width=66)"
> "  Index Cond: (id = 123456789::bigint)"
> --Total query runtime: 62 ms. 1 rows retrieved
> 
> 
> select * from newtable where id=123456789; "Append  (cost=0.00..20.19 
> rows=5 width=66)"
> "  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        Filter: (id = 123456789::bigint)"
> "  ->  Index Scan using newtable72_idversion_pk on newtable_72
(cost=0.56..20.19 rows=4 width=66)"
> "        Index Cond: (id = 123456789::bigint)"
> --Total query runtime: 156 ms. 1 rows retrieved

Notice in these results that you're not showing the command that was
executed.
The output is mostly likely from the command "ANALYZE select * from newtable
where id=123456789;" but that's not the command you claim that you ran. In
any event, the analyze output doesn't line up with the times you claim: i.e.
Analyze is showing that the first query should take about 4437 time units to
complete, and the second one should take about 20 time units, yet you claim
the second one is slower. The other queries below exhibit a similar pattern.

Are you sure you're not timing ANALYZE itself instead of the query? Because
timing "ANALYZE select * from newtable where id=123456789;" is not going to
be timing the actual time the query took to run. I would certainly expect
the _planning_ of a query against partitioned tables to take longer than
non- partitioned, but I would also expect the execution time to be the
opposite.
Hence my theory that you've accidentally timed the ANALYZE instead of the
actual running of the query. Naturally, the total query time is planning +
execution, and my experience shows that the loss in planning speed is more
than made up for by the gain in execution speed.

Perhaps you should show us the exact output of one of your tests, without
editorializing.

> I got similar results for multiple records...
> select * from oldtable where id 
> IN(10000000,1000000000,2000000000,3000000000);
> "Index Scan using oldtable_idversion_pk on oldtable  (cost=0.71..17739.18
rows=6726 width=66)"
> "  Index Cond: (id = ANY
('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> --Total query runtime: 187 ms. 4 rows retrieved
> 
> select * from newtable where id 
> IN(10000000,1000000000,2000000000,3000000000);
> "Append  (cost=0.00..933.40 rows=223 width=66)"
> "  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        Filter: (id = ANY
('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> "  ->  Index Scan using newtable01_idversion_pk on newtable_01
(cost=0.57..622.78 rows=156 width=66)"
> "        Index Cond: (id = ANY
('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> ...
> "  ->  Index Scan using newtable85_idversion_pk on newtable_85
(cost=0.57..53.37 rows=9 width=66)"
> "        Index Cond: (id = ANY
('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> --Total query runtime: 421 ms. 4 rows retrieved
> 
> 
> range of ids 
> ----------------------------------------------------------------------
> --------------------------------- select * from oldtable where id 
> between 1522999949 and 1523000049; "Index Scan using 
> oldtable_idversion_pk on oldtable  (cost=0.70..383.51 rows=144 width=66)"
> "  Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
> Total query runtime: 47 ms. 53 rows retrieved.
> 
> select * from newtable where id between 1522999949 and 1523000049; 
> "Append  (cost=0.00..408.16 rows=104 width=66)"
> "  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        Filter: ((id >= 1522999949) AND (id <= 1523000049))"
> "  ->  Index Scan using newtable51_idversion_pk on newtable_51
(cost=0.56..183.52 rows=46 width=66)"
> "        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
> "  ->  Index Scan using newtable52_idversion_pk on newtable_52
(cost=0.56..224.64 rows=57 width=66)"
> "        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
> Total query runtime: 78 ms. 53 rows retrieved.
> 
> 
> list of ids from a select clause 
> ----------------------------------------------------------------------
> --------------------------------- --Subset provides 4 ids similar but 
> not identical to the previous query select * from oldtable where id IN 
> (select * from subset); "Nested Loop  (cost=37.45..886298.00 
> rows=2028512050 width=66)"
> "  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> "  ->  Index Scan using oldtable_idversion_pk on oldtable
(cost=0.70..4414.37 rows=1693 width=66)"
> "        Index Cond: (id = subset.id)"
> Total query runtime: 171 ms. 4 rows retrieved.
> 
> select * from newtable where id IN (select * from subset) "Nested Loop  
> (cost=36.75..1407672.76 rows=1935067087 width=66)"
> "  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> "  ->  Append  (cost=0.00..7020.68 rows=1749 width=66)"
> "        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "              Filter: (subset.id = id)"
> "        ->  Index Scan using newtable01_idversion_pk on newtable_01
(cost=0.56..151.97 rows=39 width=66)"
> "              Index Cond: (id = subset.id)"
> ...
> "        ->  Index Scan using newtable86_idversion_pk on newtable_86
(cost=0.56..12.42 rows=2 width=66)"
> "              Index Cond: (id = subset.id)"
> Total query runtime: 140 ms. 4 rows retrieved.
> 
> 
> Using an index, not the primary key 
> ----------------------------------------------------------------------
> -------- --Subset provides 58 group_id pointing to 5978 records in the 
> concerned tables select * from oldtable where group_id IN (select * 
> from subset) "Nested Loop  (cost=37.33..21575715.89 rows=2028512050 
> width=66)"
> "  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> "  ->  Index Scan using oldtable_groupid_idx on oldtable
(cost=0.58..107364.99 rows=51340 width=66)"
> "        Index Cond: (group_id = subset.id)"
> Total query runtime: 3986 ms. 5978 rows retrieved.
> 
> 
> select * from newtable where group_id IN (select * from subset) "Hash 
> Join  (cost=41.25..138092255.85 rows=1935067087 width=66)"
> "  Hash Cond: (newtable.group_id = subset.id)"
> "  ->  Append  (cost=0.00..84877869.72 rows=3870134173 width=66)"
> "        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        ->  Seq Scan on newtable_01  (cost=0.00..946235.96 rows=46526896
width=66)"
> ...
> "        ->  Seq Scan on newtable_86  (cost=0.00..986527.64 rows=44269664
width=66)"
> "  ->  Hash  (cost=38.75..38.75 rows=200 width=8)"
> "        ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "              ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140
width=8)"
> Execution Cancelled after 766702 ms !
> 
> I tried the same with "SET enable_seqscan = OFF" and got an index scan 
> of all tables;
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org) To 
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bill Moran

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

--

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

Daniel Begin | 23 May 23:23 2015
Picon

Re: FW: Constraint exclusion in partitions

I am working with postgresql 9.3 and I understand from the documentation that constraint_exclusion is set to “partition” by default. Looking at my postgres.conf file, the concerned line is “#constraint_exclusion = partition”.

 

Furthermore, the execution plan shows that constraint_exclusion was used at least for constant id and range of ids

 

Daniel

 

 

 

From: pgsql-general-owner <at> postgresql.org [mailto:pgsql-general-owner <at> postgresql.org] On Behalf Of melvin6925
Sent: May-23-15 15:15
To: Daniel Begin; pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] FW: Constraint exclusion in partitions

 

Did you remember to set  constraint_exclusion = on and reload the .conf ?

 

 

 

 

Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone

-------- Original message --------
From: Daniel Begin <jfd553 <at> hotmail.com>
Date: 05/23/2015 14:37 (GMT-05:00)
To: pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] FW: Constraint exclusion in partitions

Following Francisco suggestion, I was able to do some tests earlier this morning when the partitioning process completed and all the resulting tables analyzed.

Here is what I got on both the original table and its partitioned counterpart while running the same queries. I tested them only for a couple of values but in summary...

Using a constant id:
All the queries I tried took longer on the partitioned table! I got similar results for multiple records using IN (id value1, id value2 ...)

Using a range of ids:
Surprisingly again, all the queries I tried took longer on the partitioned table!

Using a list of ids from a select clause:
More surprisingly, the queries I tried took less time on the partitioned table at least when using the primary key. Using an indexed field took so long compared to the old table that I cancelled the execution for the new one!

Guess what, I will get back to my old fat table unless someone tells me I missed something obvious!
Daniel

Note: Tables/indexes description, queries and execution plans are below.






Tables/indexes description ----------------------------------------------------------------------------------
The original table has 3870130000 records. Primary key/index on each partition queries are
ALTER TABLE oldtable ADD CONSTRAINT oldtable_idversion_pk PRIMARY KEY (id, version);
CREATE INDEX oldtable_groupid_idx ON oldtable USING btree (group_id);

The partitioned table has 3870130000 records distributed over 87 partitions. Primary key/index on each partition queries are
ALTER TABLE newtable_xx ADD CONSTRAINT newtablexx_idversion_pk PRIMARY KEY (id, version);
CREATE INDEX newtablexx_groupid_idx ON newtable_xx USING btree (group_id);
Where xx is the partition's number suffix

constant id -------------------------------------------------------------------------------------------------------
select * from oldtable where id=123456789;
"Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..4437.15 rows=1682 width=66)"
"  Index Cond: (id = 123456789::bigint)"
--Total query runtime: 62 ms. 1 rows retrieved


select * from newtable where id=123456789;
"Append  (cost=0.00..20.19 rows=5 width=66)"
"  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: (id = 123456789::bigint)"
"  ->  Index Scan using newtable72_idversion_pk on newtable_72  (cost=0.56..20.19 rows=4 width=66)"
"        Index Cond: (id = 123456789::bigint)"
--Total query runtime: 156 ms. 1 rows retrieved

I got similar results for multiple records...
select * from oldtable where id IN(10000000,1000000000,2000000000,3000000000);
"Index Scan using oldtable_idversion_pk on oldtable  (cost=0.71..17739.18 rows=6726 width=66)"
"  Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
--Total query runtime: 187 ms. 4 rows retrieved

select * from newtable where id IN(10000000,1000000000,2000000000,3000000000);
"Append  (cost=0.00..933.40 rows=223 width=66)"
"  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
"  ->  Index Scan using newtable01_idversion_pk on newtable_01  (cost=0.57..622.78 rows=156 width=66)"
"        Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
...
"  ->  Index Scan using newtable85_idversion_pk on newtable_85  (cost=0.57..53.37 rows=9 width=66)"
"        Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
--Total query runtime: 421 ms. 4 rows retrieved


range of ids -------------------------------------------------------------------------------------------------------
select * from oldtable where id between 1522999949 and 1523000049;
"Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..383.51 rows=144 width=66)"
"  Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
Total query runtime: 47 ms. 53 rows retrieved.

select * from newtable where id between 1522999949 and 1523000049;
"Append  (cost=0.00..408.16 rows=104 width=66)"
"  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        Filter: ((id >= 1522999949) AND (id <= 1523000049))"
"  ->  Index Scan using newtable51_idversion_pk on newtable_51  (cost=0.56..183.52 rows=46 width=66)"
"        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
"  ->  Index Scan using newtable52_idversion_pk on newtable_52  (cost=0.56..224.64 rows=57 width=66)"
"        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
Total query runtime: 78 ms. 53 rows retrieved.


list of ids from a select clause -------------------------------------------------------------------------------------------------------
--Subset provides 4 ids similar but not identical to the previous query
select * from oldtable where id IN (select * from subset);
"Nested Loop  (cost=37.45..886298.00 rows=2028512050 width=66)"
"  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
"  ->  Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..4414.37 rows=1693 width=66)"
"        Index Cond: (id = subset.id)"
Total query runtime: 171 ms. 4 rows retrieved.

select * from newtable where id IN (select * from subset)
"Nested Loop  (cost=36.75..1407672.76 rows=1935067087 width=66)"
"  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
"  ->  Append  (cost=0.00..7020.68 rows=1749 width=66)"
"        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"              Filter: (subset.id = id)"
"        ->  Index Scan using newtable01_idversion_pk on newtable_01  (cost=0.56..151.97 rows=39 width=66)"
"              Index Cond: (id = subset.id)"
...
"        ->  Index Scan using newtable86_idversion_pk on newtable_86  (cost=0.56..12.42 rows=2 width=66)"
"              Index Cond: (id = subset.id)"
Total query runtime: 140 ms. 4 rows retrieved.


Using an index, not the primary key ------------------------------------------------------------------------------
--Subset provides 58 group_id pointing to 5978 records in the concerned tables
select * from oldtable where group_id IN (select * from subset)
"Nested Loop  (cost=37.33..21575715.89 rows=2028512050 width=66)"
"  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
"  ->  Index Scan using oldtable_groupid_idx on oldtable  (cost=0.58..107364.99 rows=51340 width=66)"
"        Index Cond: (group_id = subset.id)"
Total query runtime: 3986 ms. 5978 rows retrieved.


select * from newtable where group_id IN (select * from subset)
"Hash Join  (cost=41.25..138092255.85 rows=1935067087 width=66)"
"  Hash Cond: (newtable.group_id = subset.id)"
"  ->  Append  (cost=0.00..84877869.72 rows=3870134173 width=66)"
"        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
"        ->  Seq Scan on newtable_01  (cost=0.00..946235.96 rows=46526896 width=66)"
...
"        ->  Seq Scan on newtable_86  (cost=0.00..986527.64 rows=44269664 width=66)"
"  ->  Hash  (cost=38.75..38.75 rows=200 width=8)"
"        ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
"              ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
Execution Cancelled after 766702 ms !

I tried the same with "SET enable_seqscan = OFF" and got an index scan of all tables;



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

Ravi Krishna | 23 May 22:34 2015
Picon

PG and undo logging

Is it true that PG does not log undo information, only redo. If true,
then how does it bring a database back to consistent state during
crash recovery. Just curious.

thanks.

--

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

twoflower | 23 May 13:23 2015
Picon

Server tries to read a different config file than it is supposed to

I thought I understood how specifying a config file path for the server works, but that's apparently not the case.

The cluster data is at /storage/postgresql/9.4/data.

The config files are at /etc/postgresql/9.4/main (this is the default location on Ubuntu).

This is how the beginning of /etc/postgresql/9.4/main/postgresql.conf looks like:

data_directory = '/storage/postgresql/9.4/data'
hba_file = '/etc/postgresql/9.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.4/main/pg_ident.conf'

So I wrote a few scripts to make my life easier, e.g. pg94start.sh:

su postgres -c "/usr/lib/postgresql/9.4/bin/pg_ctl -D /storage/postgresql/9.4/data -o '-c config_file=/etc/postgresql/9.4/main/postgresql.conf'"

But running this script did not work, the server would not start. So I checked the log file and there was:

FATAL: could not open file "/storage/postgresql/9.4/data/postgresql.conf": Permission denied

After fixing the ownership of this file, it worked.

What's the reason the server was trying to access that file? Why does not the override given by the config_file parameter work?

Thank you.
View this message in context: Server tries to read a different config file than it is supposed to
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Daniel Begin | 22 May 19:21 2015
Picon

FW: Constraint exclusion in partitions

Sent that on pgsql-novice list but did not get any answers yet.

Maybe someone could help me understand here J

 

 

Hi all,

 

I have split a large table (billions of records) into multiple partitions, hoping the access would be faster. I used an ID to make partitions check (check (id >= 100 AND id < 200)…) and created over 80 tables (children) that are now filled with data.  

 

However, after I did it, I read a second time the following sentence in the documentation and started wondering what it actually means …  “Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters)”

 

I understand that the following query will use constraint exclusion and will run faster…  

a-      Select * from parent_table where id >=9999; -- using a constant

 

But how constraint exclusion would react with the following queries …

b-      Select * from parent_table where id between 2345 and 6789; -- using a range of ids

c-       Select * from parent_table where id in(select ids from anothertable); -- using a list of ids from a select

 

Since I mostly use queries of type b and c, I am wondering if partitioning the large table was appropriate and if the queries are going to be longer to run…

Thank in advance

 

Daniel

 

Doc: http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

Melvin Davidson | 22 May 15:41 2015
Picon

Queries for unused/useless indexes


Over the years I've wrote many scripts and queries to track the database status. Recently I've had to convince a client who thought it was a good idea to create indexes for every column on every table that it is really a bad idea. To do so, I wrote useless_indexes2.sql, which shows every index that has never been scanned. They still didn't believe me, so I wrote wasted_index_space.sql. That shows how much space is wasted by all the unused indexes.

I'd like to share those queries with the community, as I know there must be others out there with the same problem.

/* useless_indexes.sql */
SELECT
       idstat.schemaname AS schema,
       idstat.relname AS table_name,
       indexrelname AS index_name,
       idstat.idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || '.' || quote_ident(idstat.relname))) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || '.' || quote_ident(indexrelname))) AS index_size,
       n_tup_upd + n_tup_ins + n_tup_del as num_writes,
       indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200
AND indexdef !~* 'unique'
ORDER BY idstat.schemaname,
         idstat.relname,
         indexrelname;

/*wasted_index_space.sql
 Requires PostgreSQL 8.4 or greater */
WITH s AS(
  SELECT SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname))::bigint) AS table_size,
       pg_size_pretty(SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname))::bigint)::bigint) AS table_size_pretty
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
   WHERE c.relkind = 'r'
   AND c.relname NOT LIKE 'pg_%'
   AND c.relname NOT LIKE 'sql%'
)
SELECT s.table_size,
       s.table_size_pretty,
       SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))::bigint) AS unused_idx_size,
       pg_size_pretty(SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))::bigint)::bigint) AS unused_idx_size_pretty,
           pg_database_size(current_database()) as db_size,
           pg_size_pretty(pg_database_size(current_database()))as db_size_pretty,
           pg_size_pretty(pg_database_size(current_database()) -  SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))::bigint)::bigint) as db_minus_wasted_space

  FROM s, pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE i.idx_scan = 0
   AND NOT idx.indisprimary
   AND NOT idx.indisunique
      GROUP BY table_size, table_size_pretty;


Melvin Davidson

Nicklas Avén | 22 May 11:51 2015
Picon

Different result depending on order of joins


Hallo

I was a little surprised by this behavior.
Is this what is supposed to happen?

This query returns what I want:

with
a as (select generate_series(1,3) a_val)
,b as (select generate_series(1,2) b_val)
,c as (select generate_series(1,1) c_val)
select * from a 
inner join c on a.a_val=c.c_val
full join b on a.a_val=b.b_val
;

I get all values from b since it only has a full join and nothing else.

But if I change the order in the joining like this:

with 
a as (select generate_series(1,3) a_val)
,b as (select generate_series(1,2) b_val)
, c as (select generate_series(1,1) c_val)
select * from a 
full join b on a.a_val=b.b_val
inner join c on a.a_val=c.c_val
;

also b is limited to only return value 1.

I thought that the join was defined by "on a.a_val=c.c_val"
and that the relation between b and the rest wasn't affected by that last inner join.

I use PostgreSQL 9.3.6

Is this the expected behavior?

Thanks

Nicklas Avén


Ted Toth | 22 May 00:25 2015
Picon

RLS policy issue

I'm work on understanding and implementing RLS. Since I work on
systems using SELinux (MLS policy) I'm using the sepgsql module that
I've modified slightly i.e. I've added a function named
sepgsql_check_row_perm that I'm using in the policy for example I have
a 'reports' table that looks like:

                                                     Table "public.reports"
     Column     |      Type       |                      Modifiers
                  | Storage  | Stats target | Description
----------------+-----------------+------------------------------------------------------+----------+--------------+-------------
 id             | integer         | not null default
nextval('reports_id_seq'::regclass) | plain    |              |
 report         | json            |
                  | extended |              |
 message_id     | integer         | not null
                  | plain    |              |
 location       | geometry(Point) |
                  | main     |              |
 security_label | text            | default sepgsql_getcon()
                  | extended |              |
Policies:
    POLICY "check_report_delete_selinux" FOR DELETE
      USING sepgsql_check_row_perm(security_label, sepgsql_getcon(),
'delete'::text)
    POLICY "check_report_insert_selinux" FOR INSERT
      WITH CHECK sepgsql_check_row_perm(security_label,
sepgsql_getcon(), 'insert'::text)
    POLICY "check_report_select_selinux" FOR SELECT
      USING sepgsql_check_row_perm(sepgsql_getcon(), security_label,
'select'::text)
    POLICY "check_report_update_selinux" FOR UPDATE
      USING sepgsql_check_row_perm(security_label, sepgsql_getcon(),
'update'::text)
      WITH CHECK sepgsql_check_row_perm(security_label,
sepgsql_getcon(), 'update'::text)

When I do a select I expect sepgsql_check_row_perm to be called and at
least output the elog message I added here's part of the patch I apply
to add the sepgsql_check_row_perm funstion to the module:

 /*
+ * BOOL sepgsql_check_row_perm(TEXT, TEXT, TEXT)
+ *
+ * Check if perm allowed for tuple.
+ * This is a variant of sepgsql_avc_check_perms_label which allows the
+ * specifying of both the source and target contexts. For MLS
+ * (write up read down) dominance purposes in the case of
+ * INSERT/UPDATE/DELETE (write) the source is the tuples context
+ * and it must dominate the peers context however in the case of
+ * SELECT (read) the source is the peers context and it must dominate
+ * the tuples context.
+ */
+PG_FUNCTION_INFO_V1(sepgsql_check_row_perm);
+Datum
+sepgsql_check_row_perm(PG_FUNCTION_ARGS)
+{
+       const char *scontext;
+       const char *tcontext;
+       const char *perm_name;
+       access_vector_t av_perm;
+
+       elog(DEBUG1, "sepgsql_check_row_perm");

I'd also expect that the "rewrite" would have added the POLICY SELECT
USING clause to the query but I don't see any indication of that in
the details that follow:

< 2015-05-21 16:59:39.030 CDT >STATEMENT:  select * from reports
< 2015-05-21 16:59:39.030 CDT >LOG:  rewritten parse tree:
< 2015-05-21 16:59:39.030 CDT >DETAIL:  (
       {QUERY
       :commandType 1
       :querySource 0
       :canSetTag true
       :utilityStmt <>
       :resultRelation 0
       :hasAggs false
       :hasWindowFuncs false
       :hasSubLinks false
       :hasDistinctOn false
       :hasRecursive false
       :hasModifyingCTE false
       :hasForUpdate false
       :hasRowSecurity true
       :cteList <>
       :rtable (
          {RTE
          :alias <>
          :eref
             {ALIAS
             :aliasname reports
             :colnames ("id" "report" "message_id" "location" "security_label")
             }
          :rtekind 0
          :relid 19116
          :relkind r
          :lateral false
          :inh true
          :inFromCl true
          :requiredPerms 2
          :checkAsUser 0
          :selectedCols (b 9 10 11 12 13)
          :modifiedCols (b)
          :securityQuals <>
          }
       )
       :jointree
          {FROMEXPR
          :fromlist (
             {RANGETBLREF
             :rtindex 1
             }
          )
          :quals <>
          }
       :targetList (
          {TARGETENTRY
          :expr
             {VAR
             :varno 1
             :varattno 1
             :vartype 23
             :vartypmod -1
             :varcollid 0
             :varlevelsup 0
             :varnoold 1
             :varoattno 1
             :location 7
             }
          :resno 1
          :resname id
          :ressortgroupref 0
          :resorigtbl 19116
          :resorigcol 1
          :resjunk false
          }
          {TARGETENTRY
          :expr
             {VAR
             :varno 1
             :varattno 2
             :vartype 114
             :vartypmod -1
             :varcollid 0
             :varlevelsup 0
             :varnoold 1
             :varoattno 2
             :location 7
             }
          :resno 2
          :resname report
          :ressortgroupref 0
          :resorigtbl 19116
          :resorigcol 2
          :resjunk false
          }
          {TARGETENTRY
          :expr
             {VAR
             :varno 1
             :varattno 3
             :vartype 23
             :vartypmod -1
             :varcollid 0
             :varlevelsup 0
             :varnoold 1
             :varoattno 3
             :location 7
             }
          :resno 3
          :resname message_id
          :ressortgroupref 0
          :resorigtbl 19116
          :resorigcol 3
          :resjunk false
          }
          {TARGETENTRY
          :expr
             {VAR
             :varno 1
             :varattno 4
             :vartype 17780
             :vartypmod 4
             :varcollid 0
             :varlevelsup 0
             :varnoold 1
             :varoattno 4
             :location 7
             }
          :resno 4
          :resname location
          :ressortgroupref 0
          :resorigtbl 19116
          :resorigcol 4
          :resjunk false
          }
          {TARGETENTRY
          :expr
             {VAR
             :varno 1
             :varattno 5
             :vartype 25
             :vartypmod -1
             :varcollid 100
             :varlevelsup 0
             :varnoold 1
             :varoattno 5
             :location 7
             }
          :resno 5
          :resname security_label
          :ressortgroupref 0
          :resorigtbl 19116
          :resorigcol 5
          :resjunk false
          }
       )
       :withCheckOptions <>
       :returningList <>
       :groupClause <>
       :havingQual <>
       :windowClause <>
       :distinctClause <>
       :sortClause <>
       :limitOffset <>
       :limitCount <>
       :rowMarks <>
       :setOperations <>
       :constraintDeps <>
       }
    )

Should I see something in the rewrite details related to the policy?
As I've shown the the table has policy defined and the details show
hasRowSecurity true why might my policy not be getting applied to the
query?

--

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


Gmane