Re: [GENERAL] Partitioning and constraint exclusion

2015-03-04 Thread Stephen Frost
Samuel, * Samuel Smith (pg...@net153.net) wrote: > I noticed that I could get very nice partition elimination using > constant values in the where clause. > > Ex: > select * from where between '2015-01-01' > and '2015-02-15' > > However, I could not get any partition elimination for queries th

Re: [GENERAL] Partitioning and constraint exclusion

2015-03-05 Thread Stephen Frost
Sam, * Samuel Smith (pg...@net153.net) wrote: > Does anyone know if there is a wishlist item for improving this in > postgresql or is this as good as it gets for now? It's absolutely on the todo list for PG to support declarative partitioning and handle these cases better. There has been a good

Re: [GENERAL] Spam on main page

2015-03-05 Thread Stephen Frost
* pinker (pin...@onet.eu) wrote: > You have spam on postgresql.org main page... > "Jim Smith: Myśli o istotnych Szczegóły kwiatów dostawy online" > It's not even proper polish :) Fixed that, sorry for not realising it earlier. We saw it on the planet side pretty quickly, just missed that it also

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

2015-03-06 Thread Stephen Frost
Matt, In your existing environment, do you have template0 set to allow connections (datallowconn)? That's not a good idea in general, but I suspect that's why pg_dumpall is including it based on a quick look at the code. Thanks! * Matt Landry (lelnet.m...@gmail.com) wrote: > Attempting to upgra

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

2015-03-06 Thread Stephen Frost
Adrian, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 03/06/2015 10:11 AM, Matt Landry wrote: > >Attempting to upgrade a large (>3TB) postgressql database from 9.3 to > >9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The > >error message instructs me to look at the last

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

2015-03-06 Thread Stephen Frost
Matt, * Matt Landry (lelnet.m...@gmail.com) wrote: > postgres=# select datname, datallowconn from pg_database ; > datname | datallowconn > ---+-- > template1 | t > template0 | t > postgres | t > reporting | t > (4 rows) Right, as I mentioned, template0 shouldn't have d

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

2015-03-06 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Matt Landry (lelnet.m...@gmail.com) wrote: > >> postgres=# select datname, datallowconn from pg_database ; > >> datname | datallowconn > >> ---+-- > >> templa

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

2015-03-06 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> Perhaps pg_upgrade should deliberately ignore template0 regardless of > >> datallowconn? And/or we should hard-wire that into pg_dumpall? > > >

Re: [GENERAL] Postgres and data warehouses

2015-03-08 Thread Stephen Frost
* Nigel Gardiner (nigelgardi...@gmail.com) wrote: > I've had a quick search and haven't seen this approach used yet, but I was > thinking, the asynchronous replication of Postgres databases could be used > as a streaming journal of changes to be processed by a data warehouse. The > other approach t

Re: [GENERAL] Strange security issue with Superuser access

2015-03-10 Thread Stephen Frost
* Andrzej Pilacik (cypise...@gmail.com) wrote: > Can anyone explain how the FK constraint function works? Is it executed as > the owner of the object. That is the only thing that would make sense for > me. Yes. Thanks, Stephen signature.asc Description: Digital signat

Re: [GENERAL] now() vs 'epoch'::timestamp

2015-04-01 Thread Stephen Frost
James, * James Cloos (cl...@jhcloos.com) wrote: > I've for some time used: > >(now()::timestamp without time zone - 'epoch'::timestamp without time > zone)::reltime::integer > > to get the current seconds since the epoch. The results are consistant > with date +%s. > > (Incidently, is the

Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

2015-05-09 Thread Stephen Frost
Yuri, Maxim, A few clarifications- * Maxim Boguk (maxim.bo...@gmail.com) wrote: > > In the meantime, I have scanned the manual for PostgreSQL 9.4 and there are > > a few things I was not able to find in the manual, my apologies if I missed > > it: > > > > 1. does PostgreSQL have parallel query c

Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

2015-05-09 Thread Stephen Frost
* Melvin Davidson (melvin6...@gmail.com) wrote: > In addition to the other great comments and advice that have been posted, > you might want to review the "Database Compatibility Technology for Oracle" > document from EnterpriseDB. > > http://www.enterprisedb.com/solutions/oracle-compatibility-tec

Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

2015-05-09 Thread Stephen Frost
* Jack Christensen (j...@jackchristensen.com) wrote: > On 05/09/2015 06:33 AM, Stephen Frost wrote: > >Temporary tables will be in memory unless they overflow work_mem > >and we do support unlogged tables and tablespaces which you could > >stick out on a ramdisk if you want.

Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

2015-05-10 Thread Stephen Frost
* Maxim Boguk (maxim.bo...@gmail.com) wrote: > On Sun, May 10, 2015 at 12:30 PM, Yuri Budilov > wrote: > > database and transaction log backup compression? not available? > > Transaction log backup compression not available (however could be easily > archived via external utilities like bzip2). >

Re: [GENERAL] PostgreSQL OGC compliant

2015-05-13 Thread Stephen Frost
Yoong Zhen, * Yoong Zhen Ang (y0z0...@gmail.com) wrote: > I would like to check whether PostgreSQL is compliant with OGC. If it is, > can you provide me the documentation to it? If you're talking about Open Geospatial Consortium, then what you really want is PostGIS, the spatial extension to Post

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

2015-05-15 Thread Stephen Frost
Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: > > On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: > > > The first is required or anyone who has done that will get the funny > > > error t

Re: [GENERAL] RLS policy issue

2015-05-21 Thread Stephen Frost
Ted, * Ted Toth (txt...@gmail.com) wrote: > 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: Just running 'explain' should show the policy. Are you running this as the owner of t

Re: [GENERAL] Row level security - notes and questions

2015-07-11 Thread Stephen Frost
Charles, * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote: > I have been testing the new row level security feature of 9.5 and I have > some notes and questions on it. Great! Glad to hear it. > My first test is to enable row level security on the table without a policy > in place. Acc

Re: [GENERAL] Creating a user for pg_start_backup

2015-07-27 Thread Stephen Frost
* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley wrote: > > Dear all, > > > > I'm setting up hot backups on my database server. As such, I'd like to set > > up a > > Postgres user that has access to only pg_start_backup and pg_stop_backup. >

Re: [GENERAL] log_statement = 'mod' does not log all data modifying statements

2015-09-09 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: > On Fri, Jun 12, 2015 at 01:54:30PM -0500, Jack Christensen wrote: > > I was recently surprised by changes that were not logged by > > log_statement = 'mod'. After changing log_statement to 'all', I > > found that the changes were occurring in a writable C

Re: [GENERAL] Use tar to online backup has an error

2015-09-24 Thread Stephen Frost
* David Steele (da...@pgmasters.net) wrote: > It's actually perfectly normal for files to disappear during a > backup, even when pg_start_backup() is called first (never perform > file-level backup with calling pg_start_backup()). The database *without* calling pg_start_backup, you mean. :) > Al

Re: [GENERAL] postgres_fdw and Kerberos authentication

2016-06-01 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Jean-Marc Lessard writes: > > A nice way to meet security requirements would be to provide single sign on > > support for the postgres_fdw. > > As long as you have defined a user in the source and destination databases, > > and configure the Kerberos auth

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-02 Thread Stephen Frost
* Sameer Kumar (sameer.ku...@ashnik.com) wrote: > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > wrote: > > Can I list all WAL files in pg_xlog by using some sql query in Postgres? > > Try > > Select pg_ls_dir('pg_xlog'); Note that this currently requires superuser privileges. Given the usefu

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-02 Thread Stephen Frost
David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost wrote: > > > * Sameer Kumar (sameer.ku...@ashnik.com) wrote: > > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > > > wrote: > > > > Can I li

Re: [GENERAL] psql remote shell command

2016-06-02 Thread Stephen Frost
* Dennis (denn...@visi.com) wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but that > executes the commands on the host where I am running psql from. Also, is it > possible for a postgres l

Re: [GENERAL] psql remote shell command

2016-06-02 Thread Stephen Frost
* David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Jun 2, 2016 at 7:30 PM, Dennis wrote: > > Is it possible to execute command in on system the is hosting postgresql > > remotely using psql or other mechanism? I know I can use \! in psql but > > that executes the commands on the ho

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-06 Thread Stephen Frost
* Vik Fearing (v...@2ndquadrant.fr) wrote: > On 03/06/16 04:32, Michael Paquier wrote: > > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar > > wrote: > >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote: > >>> Given the usefulness of this specific query and

Re: [GENERAL] postgres_fdw and Kerberos authentication

2016-06-06 Thread Stephen Frost
Jean-Marc, * Jean-Marc Lessard (jean-marc.less...@ultra-ft.com) wrote: > Stephen Frost [sfr...@snowman.net] wrote: > > The database owner operating system user has to be trusted, along with any > > superusers in the database, but if you assume those, then having PG manage &g

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-08 Thread Stephen Frost
* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost wrote: > > * Vik Fearing (v...@2ndquadrant.fr) wrote: > >> On 03/06/16 04:32, Michael Paquier wrote: > >> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar >

Re: [GENERAL] How safe is pg_basebackup + continuous archiving?

2016-06-30 Thread Stephen Frost
Greetings, * Kaixi Luo (kaixi...@gmail.com) wrote: > We use PostgreSQL at work and we do daily backups with pg_dump. After that > we pg_restore the dump and check the database that there isn't any data > corruption. As the database grows, the whole pg_dump / pg_restore cycle > time is quickly appr

Re: [GENERAL] Fastest memmove in C

2016-07-07 Thread Stephen Frost
* Merlin Moncure (mmonc...@gmail.com) wrote: > Well, testing is the key here. Microbechmarks demonstrating the value > are not enough; proven statistically relevant benchmarks generated > from postgres are the data points needed to make an assessment. My > recommendation would be to dynamically li

Re: [GENERAL] Memory usage per session

2016-07-08 Thread Stephen Frost
* amatv...@bitec.ru (amatv...@bitec.ru) wrote: > > On 08/07/2016 14:11, amatv...@bitec.ru wrote: > >> The test performs about 11K lines of code > >> Memory usage per session: > >> Oracle: about 5M > >> MSSqlServer: about 4M > >> postgreSql: about 160М > > > Visual C??? > > You will have to run Pos

Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3

2016-07-26 Thread Stephen Frost
Michael, * Michael Paquier (michael.paqu...@gmail.com) wrote: > On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch wrote: > > [Action required within 72 hours. This is a generic notification.] > > > > The above-described topic is currently a PostgreSQL 9.6 open item. Stephen, > > since you committed t

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-29 Thread Stephen Frost
Jerry, * Jerry Sievers (gsiever...@comcast.net) wrote: > Bruce Momjian writes: > > I agree, but I am not sure how to improve it. The big complaint I have > > heard is that once you upgrade and open up writes on the upgraded > > server, you can't re-apply those writes to the old server if you nee

Re: [GENERAL] Allowing multiple versions of PG under NetBSD

2016-07-29 Thread Stephen Frost
* Larry Rosenman (l...@lerctr.org) wrote: > On 2016-07-29 15:14, Bruce Momjian wrote: > >On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote: > >>>Data Directory naming, as well as keeping the init-scripts straight. > >>> > >>And who gets 5432, and Unix socket naming, it starts to get me

Re: [GENERAL] restore a specific schema from physical backup

2016-07-30 Thread Stephen Frost
* David Steele (da...@pgmasters.net) wrote: > On 7/29/16 5:31 PM, Rakesh Kumar wrote: > > Sure. > > > > 1 - You ran pg_basebackup on node-1 against a live cluster and store > > it on NFS or tape. > > 2 - Do a restore on node-2 from the backup taken on (1), but only for > > a subset of the databas

Re: [GENERAL] Privileges on public schema can't be revoked?

2016-09-06 Thread Stephen Frost
Greg, * Greg Fodor (gfo...@gmail.com) wrote: > Apologies in advance about this since it is likely something obvious, > but I am seeing some very basic behavior that does not make sense. > I've tested this on a fresh build of 9.6rc1 and also 9.1.24 (just to > see if it was a regression.) After crea

Re: [GENERAL] Privileges on public schema can't be revoked?

2016-09-06 Thread Stephen Frost
Gregm * Greg Fodor (gfo...@gmail.com) wrote: > A, I wasn't aware of the PUBLIC meta-role. Not sure if it's useful > feedback, I spent a lot of time digging around the web for solutions > that would basically let me query the database to see all of the > effective privileges for a user, and non

Re: [GENERAL] Multi tenancy : schema vs databases

2016-09-30 Thread Stephen Frost
* Jeff Janes (jeff.ja...@gmail.com) wrote: > On Fri, Sep 30, 2016 at 2:06 AM, Rakesh Kumar > wrote: > > We require complete data isolation. Absolutely nothing should be shared > > between two tenants. > > Then you need different clusters per tenant. Otherwise, the WAL records of > different tena

Re: [GENERAL] BRIN indexes and ORDER BY

2016-10-05 Thread Stephen Frost
Darren, * Darren Lafreniere (dlafreni...@onezero.com) wrote: > Tom Lane wrote: > > > Gavin Wahl wrote: > > >> It seems trivial to accelerate a MAX or MIN query with a BRIN index. You > > >> just find the page range with the largest/smallest value, and then only > > >> scan that one. Would that be

Re: [GENERAL] avoiding index on incremental column

2016-10-17 Thread Stephen Frost
* t.dalpo...@gmail.com (t.dalpo...@gmail.com) wrote: > I've a very huge table whose 1st column is a numeric value, starting > from 0 at the 1st row and incremented by 1 each new row I inserted. > No holes, no duplicates. > I need to perform some very fast query based on this value, mainly > around

Re: [GENERAL] Row level security performance joining large tables

2016-11-10 Thread Stephen Frost
David, * David R. Pike (david.p...@trustedconcepts.com) wrote: > From what I can understand the RLS implementation strives to execute policy > checks before user provided predicate checks so as to avoid leaking protected > data. Is there any way to make the join look "safe" to the optimizer to

Re: [GENERAL] Postgresql 9.6 and Big Data

2016-12-02 Thread Stephen Frost
Job, * Job (j...@colliniconsulting.it) wrote: > we are planning to store historically data into a Postgresql 9.6 table. The question is less about what you're storing in PG and more about what you're going to be doing with that data. > We see on Postgresql limit that it could handle "big data".

Re: [GENERAL] Recursive row level security policy

2016-12-17 Thread Stephen Frost
Simon, * Simon Charette (charett...@gmail.com) wrote: > Ahh makes sense, thanks for the explanation! > > I was assuming USING() clauses were executed in the context of the > owner of the policy, by passing RLS. No, as with views, a USING() clause is executed as the caller not the owner of the re

Re: [GENERAL] Is there a reason the "-v/verbose" switch is not printed with pg_dumpall --help

2016-12-23 Thread Stephen Frost
Daniel, * Daniel Westermann (daniel.westerm...@dbi-services.com) wrote: > postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall -V > pg_dumpall (PostgreSQL) 9.6.1 > postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall --help > pg_dumpall extracts a PostgreSQL database cluster

Re: [GENERAL] Querying dead rows

2016-12-23 Thread Stephen Frost
* Rakesh Kumar (rakeshkumar...@outlook.com) wrote: > Is there a way to query dead rows (that is, rows which are dead and still not > cleaned up by Vacuum) using SQL. I am asking this just as an academical > question. CREATE EXTENSION pageinspect; https://www.postgresql.org/docs/9.6/static/page

Re: [GENERAL] Is there a reason the "-v/verbose" switch is not printed with pg_dumpall --help

2016-12-24 Thread Stephen Frost
Daniel, * Stephen Frost (sfr...@snowman.net) wrote: > * Daniel Westermann (daniel.westerm...@dbi-services.com) wrote: > > postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall -V > > pg_dumpall (PostgreSQL) 9.6.1 > > postgres@pgbox:/u01/app/postgres/local/dmk/ [PG9

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

2016-12-30 Thread Stephen Frost
Greetings, * Guyren Howe (guy...@gmail.com) wrote: > it occurs to me to wonder whether it is practical to use PG’s own roles and > security model in lieu of using an application-level one. The short answer is yes. > It seems that the role system in PG is sufficient for most general purposes. >

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

2017-01-11 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Guyren Howe writes: > > I’m not following. What I would like is just a lightweight way to switch > > the connections to use a different role, or some moral equivalent, that > > would prevent an SQL injection from wrecking havoc. I’m not proposing > > any

Re: [GENERAL] Combining count() and row_number() as window functions

2017-01-19 Thread Stephen Frost
Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Thomas Kellerer writes: > > I assumed that the count() wouldn't increase the runtime of the query as > > the result of the row_number() can be used to calculate that. > > No such knowledge exists in Postgres. Given our general approach in which >

Re: [GENERAL] PgPool or alternatives

2017-01-21 Thread Stephen Frost
Simon, * Simon Windsor (simon.wind...@cornfield.me.uk) wrote: > My employer wants to move from an in house Oracle solution to a > cloud based Postgres system. The system will involve a number of > data loaders running 24x7 feeding several Postgres Databases that > will be used by internal applicat

Re: [GENERAL] Doubts regarding postgres Security

2017-01-21 Thread Stephen Frost
Pawan, * PAWAN SHARMA (er.pawanshr0...@gmail.com) wrote: > 1. How can we set user account block feature after max number of > invalid password entries? There are ways to accomplish this, but they're unfortunately complicated. In the past, I've implemented these kinds of requirments by using the

Re: [GENERAL] Doubts regarding postgres Security

2017-01-21 Thread Stephen Frost
Pawan, Please be sure to include the mailing list on replies, so others can benefit from the discussion. Also, please reply in-line, as I do below, instead of top-posting. * PAWAN SHARMA (er.pawanshr0...@gmail.com) wrote: > So, there is no solution for my first question, we need if users enter t

Re: [GENERAL] Doubts regarding postgres Security

2017-01-21 Thread Stephen Frost
* Thomas Kellerer (spam_ea...@gmx.net) wrote: > Stephen Frost schrieb am 21.01.2017 um 22:37: > >>So, there is no solution for my first question, we need if users enter the > >>wrong password more than 5 times than their account gets locked and then > >>only DBA will

Re: [GENERAL] Partitioned "views"

2017-01-22 Thread Stephen Frost
Greetings, * Job (j...@colliniconsulting.it) wrote: > we use a materialized view to aggregate datas from a very big table > containing logs. > The source table is partitioned, one table for a day. > > Since the refresh of the materialized view seems to grow a lot about timing, > we would like t

Re: R: [GENERAL] Partitioned "views"

2017-01-22 Thread Stephen Frost
Greetings, * Job (j...@colliniconsulting.it) wrote: > >>The mat view takes longer and longer to update because it runs the full > >>query. What you really want to do is have a side-table that you update > >>regularly with appropriate SQL to issue UPDATE statements for just the > >>current day

Re: [GENERAL] Can we not give tyrannical pedants control of #postgresql?

2017-02-01 Thread Stephen Frost
Greetings, * Merlin Moncure (mmonc...@gmail.com) wrote: > On Thu, Jan 19, 2017 at 5:23 PM, Julian Paul wrote: > > I hope that particular stereotypes aren't proven here, but it appears > > #postgresql encourages a particular tier and makes aware of it's rigid > > hierarchy. I owe alot to #postgres

Re: [GENERAL] Can row level security policies also be implemented for views?

2015-11-25 Thread Stephen Frost
Caleb, * Caleb Meredith (calebmeredi...@gmail.com) wrote: > I'm developing an application where strict control of my data is important. > Views allow me to build a strict custom reading experience, allowing me to > add computed columns and hide private and metadata columns. Row level > security al

Re: [GENERAL] "trust" authentication in pg_hba.conf

2015-11-25 Thread Stephen Frost
* Chris Withers (ch...@simplistix.co.uk) wrote: > What's the default contents of pg_hba.conf that postgres ships with? The PG community provides both source code, which is expected to be used by developers and is therefore wide open, and binary packages, which are expected to be used by end users

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

2015-12-15 Thread Stephen Frost
Benjamin, * Benjamin Smith (li...@benjamindsmith.com) wrote: > Is there a way to set PG field-level read permissions so that a deny doesn't > cause the query to bomb, but the fields for which permission is denied to be > nullified? Not directly, no. One approach would be to create views which

Re: [GENERAL] Check old and new tuple in row-level policy?

2015-12-16 Thread Stephen Frost
Karl, * Karl Czajkowski (kar...@isi.edu) wrote: > I've been trying to learn more about the row-security policies but > coming up short in my searches. Was there any consideration to > allowing access to both old and new row tuples in a POLICY ... CHECK > expression? This idiom I've seen in the l

Re: [GENERAL] Check old and new tuple in row-level policy?

2015-12-16 Thread Stephen Frost
* Karl Czajkowski (kar...@isi.edu) wrote: > I think that there is significant overlap between authorization, state > transition models, and data integrity constraints once you start > considering collaborative applications with mutable records. Even with OLD/NEW being available to UPDATE, many of

Re: [GENERAL] Check old and new tuple in row-level policy?

2015-12-18 Thread Stephen Frost
Karl, * Karl Czajkowski (kar...@isi.edu) wrote: > Ideally, I'd be able to write a policy that has conditions for each > category of operation: > >POLICY FOR SELECT WITH expr1 >POLICY FOR INSERT WITH expr2 >POLICY FOR DELETE WITH expr3 >POLICY FOR UPDATE WITH expr4 It's possible

Re: [GENERAL] Check old and new tuple in row-level policy?

2015-12-18 Thread Stephen Frost
Karl, * Karl Czajkowski (kar...@isi.edu) wrote: > On Dec 18, Stephen Frost modulated: > > Any UPDATE which requires SELECT rights on the table will require expr1 > > to pass AND expr4 (the UPDATE's USING clause) to pass. This is modeled > > directly off of our exist

Re: [GENERAL] Session Identifiers

2015-12-21 Thread Stephen Frost
Oleg, * oleg yusim (olegyu...@gmail.com) wrote: > tcp_keepalives_idle = 900 > tcp_keepalives_interval=0 > tcp_keepalives_count=0 > > Doesn't terminate connection to database in 15 minutes of inactivity of > psql prompt. So, it looks like that would work only for case if network > connection is br

Re: [GENERAL] 9.5rc1 RLS select policy on insert?

2016-01-06 Thread Stephen Frost
* Ted Toth (txt...@gmail.com) wrote: > I see the insert policy check running but also the select policy using > on insert. I don't understand why the select policy is being run. > Could it possibly be related to using a sequence on the table? It's used when SELECT rights are required on the table,

Re: [GENERAL] 9.5rc1 RLS select policy on insert?

2016-01-07 Thread Stephen Frost
Ted, Please don't top-post on these lists. > On Wed, Jan 6, 2016 at 9:40 PM, Stephen Frost wrote: > > * Ted Toth (txt...@gmail.com) wrote: > >> I see the insert policy check running but also the select policy using > >> on insert. I don't understa

Re: [GENERAL] Why is my database so big?

2016-02-22 Thread Stephen Frost
All, * FarjadFarid(ChkNet) (farjad.fa...@checknetworks.com) wrote: > Tom, thanks for your unbiased detailed response. > > Interesting post. Please don't top-post. My comments are in-line, below. > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-o

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

2016-02-22 Thread Stephen Frost
Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Seamus Abshere writes: > > I don't understand why the query planner is choosing a BitmapAnd when an > > Index Scan followed by a filter is obviously better. > > > On Postgres 9.4.4 with 244gb memory and SSDs > > > maintenance_work_mem 100 >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

2016-02-22 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I've not looked into the specific costing here to see why the BitmapAnd > > ended up being chosen over just doing an index scan with the btree and > > then filtering, but I do believe it to be a problem a

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

2016-02-22 Thread Stephen Frost
* Seamus Abshere (sea...@abshere.net) wrote: > Is there any other way to differentiate the 2 index scans? FWIW, 10% of > houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to > drop the index like Tom said.) Have to admit that I continue to be interested in this as it might relat

Re: [GENERAL] Get the date of creation of objects in the database

2016-02-22 Thread Stephen Frost
David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > The only source of data for that question is the local filesystem. If > that is acceptable you can find examples online provided to others who have > asked this question. What on the local filesystem would help here..? All you kno

Re: [GENERAL] Get the date of creation of objects in the database

2016-02-22 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > At least on a first blush look through the threads linked from such a > > search, I'm unimpressed by the arguments against and note that there are > > quite a few arguments for. > > I think you mis

Re: [GENERAL] "plan should not reference subplan's variable" when using row level security

2016-02-24 Thread Stephen Frost
Adam, * Adam Guthrie (asguth...@gmail.com) wrote: > psql:/tmp/test.sql:26: ERROR: plan should not reference subplan's variable > > Is this a bug or am I doing something wrong? Yeah, looks like a bug to me. My gut reaction is that we're pulling up a subquery in a way that isn't possible and tha

Re: [GENERAL] "plan should not reference subplan's variable" when using row level security

2016-02-24 Thread Stephen Frost
Adrian, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > I started to work through this when I realized the > permissions/attributes of the role test are not shown. This seems to > be important as the UPDATE example works if you run it immediately > after: > > INSERT INTO b (id, a_id, text) V

Re: [GENERAL] "plan should not reference subplan's variable" when using row level security

2016-02-24 Thread Stephen Frost
* Adam Guthrie (asguth...@gmail.com) wrote: > On 24 February 2016 at 20:27, Stephen Frost wrote: > > Yeah, looks like a bug to me. My gut reaction is that we're pulling up > > a subquery in a way that isn't possible and that plan shouldn't be > > getting bui

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

2016-02-29 Thread Stephen Frost
* David G. Johnston (david.g.johns...@gmail.com) wrote: > Given the amount of damage a person with write access to a table can get > into it seems pointless to not allow them to analyze the table after their > updates - since best practices would say that normal work with a table > should not be pe

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

2016-02-29 Thread Stephen Frost
* David G. Johnston (david.g.johns...@gmail.com) wrote: > On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost wrote: > > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > > Given the amount of damage a person with write access to a table can get > > > into i

Re: [GENERAL] UPSERT and HOT-update

2016-03-19 Thread Stephen Frost
Daniel, * CHENG Yuk-Pong, Daniel (j16s...@gmail.com) wrote: > I am doing a INSERT...ON CONFLICT...UPDATE.. on a table. The query is > mostly-UPDATE and does not change any columns most of the time, like > so: > > CREATE INDEX ON book(title); > INSERT INTO book (isbn, title, author, lastupdat

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

2016-03-24 Thread Stephen Frost
David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > Which means that, aside from effort, the main blocking factors here are > code complexity (which I understand) and limited grant "bits" as Stephen > puts it. So I pose the question: do any of the committers consider a grant > bit to

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

2016-03-25 Thread Stephen Frost
David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Mar 24, 2016 at 4:51 AM, Stephen Frost wrote: > > I don't see any reason why the patch itself would be terribly difficult, > > but are we sure we'd want just ANALYZE and not VACUUM also? Which wou

Re: [GENERAL] Really unique session ID - PID + connection timestamp?

2016-04-09 Thread Stephen Frost
Greetings, * Durumdara (durumd...@gmail.com) wrote: > Where I can get timestamp or some other unique data with I can create a > combined primary key? Not entirely sure about the rest of it, but if you want backend start time, you can look at pg_stat_activity (which also happens to have the pid).

Re: [GENERAL] Really unique session ID - PID + connection timestamp?

2016-04-09 Thread Stephen Frost
* Christoph Berg (c...@df7cb.de) wrote: > Re: Durumdara 2016-04-09 > > > In MS the session id is smallint, so it can repeats after server restarts, > > but my coll. found a "session creation timestamp". > > This is a key which unique. > > With this we can check for died sessions and we can clean

Re: [GENERAL] Postgresql 9.3.4 file system compatibility

2016-04-11 Thread Stephen Frost
* Albe Laurenz (laurenz.a...@wien.gv.at) wrote: > Marllius wrote: > > OCFS2 = oracle cluster file system 2 > > I think using OCFS2 for PostgreSQL data is a good idea if you want > to be the first at something or try to discover bugs in OCFS2. I've found that OCFS2 is a very decent clustered files

Re: [GENERAL] Enhancement Request

2016-04-20 Thread Stephen Frost
Rob, * Rob Brucks (rob.bru...@rackspace.com) wrote: > I'd like to propose two enhancements to the PostgreSQL code, but I'm not sure > if this is the correct mailing list. So if it's not then please let me know > where I need to post this. This is the correct place. I don't know why people are

Re: [GENERAL] Clarify "allow_system_table_mods"

2016-04-25 Thread Stephen Frost
David, Melvin, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Monday, April 25, 2016, Melvin Davidson wrote: > > I need clarification on allow_system_table_mods parameter > > Per the documentation: > > *Allows modification of the structure of system tables.* This is used by > > init

Re: [GENERAL] Clarify "allow_system_table_mods"

2016-04-25 Thread Stephen Frost
* Melvin Davidson (melvin6...@gmail.com) wrote: > Then could you please clarify exactly what structural mods are permitted by > *"* > *Allows modification of the structure of system tables" ?* I would say, in short, those modifications which are implemented by PG developers who know what's safe to

Re: [GENERAL] Clarify "allow_system_table_mods"

2016-04-25 Thread Stephen Frost
Melvin, * Melvin Davidson (melvin6...@gmail.com) wrote: > So in essence "*Allows modification of the structure of system tables" does > NOT allow _structural_ changes (probably only dml changes) > and the documentation should be changed to clarify. That would imply that other changes are acceptab

Re: [GENERAL] Clarify "allow_system_table_mods"

2016-04-25 Thread Stephen Frost
* Melvin Davidson (melvin6...@gmail.com) wrote: > Hmmm, if you go back a few comments, you will note that per initdb --help > there is no such option available. It's not an option *to* initdb, it's an option which is used *by* initdb. I'm afraid I'm done with this particular discussion. Hopefull

Re: [GENERAL] missing public on schema public

2017-11-14 Thread Stephen Frost
Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > =?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?= writes: > > I have some additional info and a fix. > > Firstly steps to reproduce: > > Yeah, I can reproduce this. I suspect it got broken by Stephen's hacking > around with default ACLs. A simple example

Re: [GENERAL] Query on pg_settings view

2017-11-15 Thread Stephen Frost
Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > Ronen Nofar wrote: > > I have a weird case when running a query on the pg_settings view. > > I have two users, first one is the default user - postgres which is a > > superuser > > and another one is a role which i had created, i call

[GENERAL] Migration to pglister - Before

2017-11-20 Thread Stephen Frost
Greetings, We will be migrating these lists to pglister in the next few minutes. This final email on the old list system is intended to let you know that future emails will have different headers and you will need to adjust your filters. The changes which we expect to be most significant to user

Re: [GENERAL] ISO TESTS for a Pg lexer+parser

2007-07-06 Thread Stephen Frost
* Kynn Jones ([EMAIL PROTECTED]) wrote: > Hi! I am in the process of writing a PostgreSQL lexer/parser in Perl, > because everything else I've found in this area is too buggy. I'm > basing this lexer/parser on the lexer and parser encoded respectively > in scan.l and gram.y under src/backend/pars

Re: [GENERAL] returns setof rec... simple exampe doesn't work

2007-07-12 Thread Stephen Frost
* Gauthier, Dave ([EMAIL PROTECTED]) wrote: > stdb=# select myfunc(); > ERROR: set-valued function called in context that cannot accept a set select * from myfunc(); ? Stephen signature.asc Description: Digital signature

Re: [GENERAL] optimizing postgres

2007-07-12 Thread Stephen Frost
* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: > Since I'm not an expert in Postgres database design, I'm assuming I've > done something sub-optimal. Are there some common techniques for > tuning postgres performance? Do we need beefier hardware? Honestly, it sounds like the database design might

Re: [GENERAL] question on scan of clustered index

2007-07-12 Thread Stephen Frost
* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: > I am running three ways: sequential scan, bitmap index scan and index scan. > The I/O cost for the index scan is 24+ times more than the other two. I do > not > understand why this happens. If I am using a clustered index, it is my > understandin

Re: [GENERAL] doubt

2007-07-12 Thread Stephen Frost
* Narasimha Rao P.A ([EMAIL PROTECTED]) wrote: > Does postgreSQL support distributive query processing? PostgreSQL does not directly support splitting one query across multiple nodes (cpus, machines, whatever). It's certainly possible to set up distributed load balancing over some set of PostgreS

Re: [GENERAL] How to pg_dumpall without root password

2007-07-12 Thread Stephen Frost
* Tom Lane ([EMAIL PROTECTED]) wrote: > If you're worried about having the system insecure even transiently > against local bad guys, it's possible to do this without opening any > hole, but it requires taking the DB down for a few minutes so you can > do the password change in standalone mode. An

Re: [GENERAL] Debian problem...

2007-09-10 Thread Stephen Frost
* Tom Allison ([EMAIL PROTECTED]) wrote: > I hosed up postgresql by deleting the data directory. erp. That's no good. > So I thought I would just uninstall and reinstall postgres using Debian > packages. > Now I have nothing working. Huh, odd, that'd normally work, I think. > Wondering if any

  1   2   3   4   5   >