Re: [GENERAL] type "xxxxxxx" does not exist

[Micky Hulse]

Hi Adrian,

On Fri, May 19, 2017 at 3:02 PM, Adrian Klaver
 wrote:
> FYI, 9.3 is now up to 9.3.17:
> At some point, once you have gotten a handle on using Postgres, you should
> probably update. Read the Release Notes for each of the minor releases to
> see what has been fixed.

Ah, good to know! Thank you for tip! :)

> you where connecting using local, which is the socket connection.
> If you had done:
> psql -U otherusername -d database -h localhost
> it would have asked for a password(md5 auth method). If otherusername does
> not have the LOGIN attribute you would not been able to log in anyway. For
> more detailed information see:
> https://www.postgresql.org/docs/9.3/static/sql-createrole.html

Ahhh, that makes sense! Thank you for clarifying and for the linkage,
I really appreciate it!

Thanks to everyone for all of the help!


-- 
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] Partitioned Data and Locking

[Tom Lane]

Ed Behn  writes:
> If I run EXPLAIN on the query, I get a result that shows that only the
> child tables whose CHECKs are consistent with the WHERE clause are
> searched. This is exactly what I expected.
> However, when I run the query, AccessShareLocks are obtained by the
> transaction for all child tables (and their indices).

> Am I misunderstanding something? I seems that these locks shouldn't exist
> if the query plan doesn't use most of the child tables.

Nope, they must exist, because the planner has to examine those tables
to discover that their constraints allow skipping them at execution.

> If this is a bug, perhaps it could be fixed in a future release.

This is not a bug.  You might argue that we could release a child table's
lock once we've proven that we need not scan that table, but that's
fraught with theoretical and practical difficulties.  As one example,
once we've released that lock, someone could change the child's
constraint, invalidating the proof.  (Indeed, since AccessShareLock is
such a weak lock, it would more or less require DDL on the child table
for there to be any conflict.)

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] Window functions can't be used as LIMIT/FETCH FIRST alternative

[David G. Johnston]

On Sat, May 20, 2017 at 2:50 AM, Navrotskiy Artem  wrote:

> This is especially unpleasant for expressions using RANK and DENSE_RANK,
> which can not simply be rewritten using LIMIT.
>

​They cannot anyway, at least not DENSE_RANK with ties.​

Others will have to speak to the dynamics involved with the overall current
behavior.

David J.

[GENERAL] Window functions can't be used as LIMIT/FETCH FIRST alternative

[Navrotskiy Artem]

Hello. I found unexpected query optimization issue: window functions can't be used as LIMIT/FETCH FIRST alternative (as far as I know, before SQL:2008 it was the only standard way to implement LIMIT in the query). The problem is that PostgreSQL does not stop reading the records after reaching the limit specified in the WHERE clause. This is especially unpleasant for expressions using RANK and DENSE_RANK, which can not simply be rewritten using LIMIT. For example: test=# SELECT version();                                                     version                                                     - PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit(1 строка) test=# EXPLAIN ANALYZESELECT * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY id) n,  posts.*FROM posts) pWHERE n <= 10ORDER BY id;                                                                    QUERY PLAN                                                                     --- Subquery Scan on p  (cost=0.42..146174.41 rows=334029 width=690) (actual time=0.073..1037.148 rows=10 loops=1)   Filter: (p.n <= 10)   Rows Removed by Filter: 90   ->  WindowAgg  (cost=0.42..133648.34 rows=1002086 width=690) (actual time=0.069..953.048 rows=100 loops=1)         ->  Index Scan using posts_pkey on posts  (cost=0.42..118617.05 rows=1002086 width=682) (actual time=0.059..503.496 rows=100 loops=1) Planning time: 0.206 ms Execution time: 1037.199 ms(7 rows) test=# EXPLAIN ANALYZESELECT * FROM postsORDER BY idLIMIT 10;                                                              QUERY PLAN                                                              -- Limit  (cost=0.42..1.61 rows=10 width=682) (actual time=0.021..0.031 rows=10 loops=1)   ->  Index Scan using posts_pkey on posts  (cost=0.42..118617.05 rows=1002086 width=682) (actual time=0.020..0.028 rows=10 loops=1) Planning time: 0.145 ms Execution time: 0.065 ms(4 rows) test=#  -- С уважением,Навроцкий Артем+7 (925) 095-80-41 

[GENERAL] Partitioned Data and Locking

[Ed Behn]

I've found what to me is a surprising locking behavior when querying
partitioned data as described in section 5.10 of the User's Manual.

I have an empty parent table with a number of child tables containing data.
Each child has a CHECK condition on the relevant column. I am executing a
SELECT query against the parent table with a condition on the column in the
CHECK in the WHERE clause.

I have constraint_exclusion set to partition.

If I run EXPLAIN on the query, I get a result that shows that only the
child tables whose CHECKs are consistent with the WHERE clause are
searched. This is exactly what I expected.
However, when I run the query, AccessShareLocks are obtained by the
transaction for all child tables (and their indices).

Am I misunderstanding something? I seems that these locks shouldn't exist
if the query plan doesn't use most of the child tables.

If this is a bug, perhaps it could be fixed in a future release. I would be
beneficial to my application, as we most often are only writing to one
partition in any given day. If a query against older data is running, the
write transaction could still proceed.
 -Ed

-- 

Ed Behn / Staff Engineer / Airline and Network Services

Information Management Services

2551 Riva Road, Annapolis, MD 21401 USA

Phone: 410-266-4426 / Cell: 240-696-7443

ed.b...@rockwellcollins.com



www.rockwellcollins.com

Re: [GENERAL] [OT] Help: stories of database security and privacy

[Bruno Wolff III]

On Tue, Apr 11, 2017 at 21:48:58 +0200,
 Lifepillar  wrote:


I'd like to take the opportunity to also engage students about the topic
of privacy (or lack thereof). So, I am here to ask if you have
interesting/(in)famous stories to share on database security/privacy
"gone wrong" or "done right"(tm), possibly with technical details (not
necessarily to share with the students, but for me to understand the
problems). I am asking to this list because I will use PostgreSQL, so
maybe I can collect ideas that I can implement or demonstrate in
practice.


"Translucent Databases" has some interesting ideas about providing privacy 
by operating directly on encrypted data (without decrypting it) so that 
information is kept private even from the database. The are major 
limitations on what you can do, but there may be some cases where the 
techniques can be used.



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

[GENERAL] Beta rpms for Fedora are missing (at this time)

[Bruno Wolff III]

This is probably a temporary build problem, but I thought mentioning 
here might get it fixed faster in case it hasn't already been noticed. 
https://download.postgresql.org/pub/repos/yum/testing/10/fedora/fedora-25-x86_64/ 
should have rpms but doesn't. I am using test rpms I got from there about 
a week ago, but wanted to switch to the prebuilt beta version.



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