[GENERAL] Migration to pglister - Before

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

Re: [GENERAL] Query on pg_settings view

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

Re: [GENERAL] missing public on schema public

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

Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?

Greetings, * Ivan Voras (ivo...@gmail.com) wrote: > On 30 October 2017 at 22:10, David G. Johnston > wrote: > > ​Not quite following but ownership is an inheritable permission; > > Basically, I'm asking if "ownership" can be revoked from the set of > inherited

Re: [GENERAL] pg_audit to mask literal sql

Greetings, * rakeshkumar464 (rakeshkumar...@outlook.com) wrote: > By mask I mean pgaudit should log where ssn = '123-456-7891' as where ssn = > '?' Data masking really isn't part of auditing, and so even if pgaudit could do so, that wouldn't really be the right place to make it happen. There

Re: [GENERAL] Backup strategy using 'wal_keep_segments'

Greetings, * Rhhh Lin (ruanline...@hotmail.com) wrote: > I would actually be an advocate for using a proper archive_command in order > to facilitate a proper (Per the documentation) PITR and backup strategy. Glad to hear it. > However, a colleague had suggested such a creative approach

Re: [GENERAL] Backup strategy using 'wal_keep_segments'

Greetings, * Rhhh Lin (ruanline...@hotmail.com) wrote: > A colleague recently suggested that instead of implementing an > 'archive_command' to push archivable WALs to a secondary location (for > further backup to tape for example), we could instead persist the WAL files > in their current

Re: [GENERAL] pgpass file type restrictions

Matt, * Desidero (desid...@gmail.com) wrote: > I agree that it would be better for us to use something other than LDAP, If you happen to be using Active Directory, then you should really be using Kerberos-based auth instead. AD includes both LDAP and a KDC and the LDAP half is really *not* the

Re: [GENERAL] Permissions for Web App

Greetings, * Igal @ Lucee.org (i...@lucee.org) wrote: > It worked, thanks! Be sure to check that you're really getting what you want here. > For future reference and for the benefit of others, the command that > I ran is: > >   ALTER DEFAULT PRIVILEGES IN SCHEMA public >     GRANT SELECT,

Re: [GENERAL] pg_start/stop_backup naming conventions

Greetings, * mj0nes (matthew.jo...@ramtech.co.uk) wrote: > I'm just starting out on a rolling backup strategy and the naming convention > has thrown me slightly for the WAL and "backup_label" files. > > What I want to do is pair up the backup label files with the associated tar > ball of the

Re: [GENERAL] Using cp to back up a database?

Ron, * Ron Johnson (ron.l.john...@cox.net) wrote: > Maybe my original question wasn't clear, so I'll try again: is it > safe to do a physical using cp (as opposed to rsync)? Frankly, I'd say no. There's nothing to guarantee that the backup is actually sync'd out to disk. Further, you're

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

Greetings John, * John R Pierce (pie...@hogranch.com) wrote: > On 9/20/2017 6:55 AM, Stephen Frost wrote: > >If AD is in the mix here, then there's no need to have things happening > >at the database level when it comes to passwords- configure PG to use > >Kerberos and

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

John, * John R Pierce (pie...@hogranch.com) wrote: > On 9/19/2017 3:32 PM, chiru r wrote: > >How those application accounts get recognized in database? > > > >Let say  App_user1 authenticated through application ,after that > >how the App_user1 get access to DB? > > > >can you please provide more

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > > chiru r <chir...@gmail.com> writes: > > > > We are looking for User profiles in ope so

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > chiru r writes: > > We are looking for User profiles in ope source PostgreSQL. > > For example, If a user password failed n+ times while login ,the user > > access has to be blocked few seconds. > > Please let us know, is there

Re: [GENERAL] Logging the fact that a log was shipped

* Christoph Moench-Tegeder (c...@burggraben.net) wrote: > ## Stephen Frost (sfr...@snowman.net): > > > Worse, such scripts run the serious risk of losing WAL if a crash > > happens because nothing is ensuring that the WAL has been sync'd to disk > > before returning

Re: [GENERAL] Logging the fact that a log was shipped

* Christoph Moench-Tegeder (c...@burggraben.net) wrote: > ## Ron Johnson (ron.l.john...@cox.net): > > > How is this done in v8.4? (I tried adding "date; rsync ..." but pg > > didn't like that *at all*.) > > There's a DEBUG1-level log message on successful archive_command > completion - that

Re: [GENERAL] PG and database encryption

Greetings, * Scott Marlowe (scott.marl...@gmail.com) wrote: > On Tue, Aug 22, 2017 at 3:13 PM, PT wrote: > > On Tue, 22 Aug 2017 12:48:13 -0700 (MST) > > rakeshkumar464 wrote: > >> We have a requirement to encrypt the entire database. What

Re: [GENERAL] Planner statistics usage for composite type

Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Dmitry Lazurkin writes: > > I am trying to find workaround for cross-column statistics. > > ... > > Worn estimate. Planner doesn't use statistics. In code I see usage of > > function scalargtsel which returns default selectivity

Re: [GENERAL] LDAP authentication without OU in ldapbasedn

Greetings, * Gregory Nicol (gregory.ni...@medbank.com.mt) wrote: > I can't seem to get LDAP Authentication working without an OU in the > ldapbasedn. My users are spread across multiple OUs without a common root OU > which is why I'm trying to authenticate with just the DC. As it looks like

Re: [GENERAL] Associating a basebackup and it's .backup WAL file

Greetings, * cen (imba...@gmail.com) wrote: > Seems like a lot of manual work to me, to automate it I'd basically > have to diff both directories and then copy only the newest > differences over to the recovery. So far I was unable to find a > supersecret git repo with bash scripts accomplishing

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Greetings, * mariusz (mar...@mtvk.pl) wrote: > On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost wrote: > > > How is this done inside a shell script? > > > > Generally, it's not. I suppose it might be possible to use '\!' with > > psql and then have a shell sn

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Melvin, * Melvin Davidson (melvin6...@gmail.com) wrote: > On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost <sfr...@snowman.net> wrote: > >Part of my concern is that such a script is unlikely to show any problems > until it comes time to do a restore > As previously stated

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Greetings, * Melvin Davidson (melvin6...@gmail.com) wrote: > Stephen, > >This script is a good example of why trying to take a PG backup using > shell scripts isn't a good idea. > > Your criticism is noted, however, I have used it many times in the past > with absolutely no problem. I submitted

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Greetings, * Melvin Davidson (melvin6...@gmail.com) wrote: > On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfr...@snowman.net> wrote: > > I'd recommend considering one of the existing PG backup tools which know > > how to properly perform WAL archiving and tracking

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Greetings, * hvjunk (hvj...@gmail.com) wrote: > I’ve previously done ZFS snapshot backups like this: > > psql -c “select pg_start_backup(‘snapshot’);” > zfs snapshot TANK/postgresql@`date ‘+%Ymd’` > psql -c “select * from pg_stop_backup();” Hopefully you are also doing WAL archiving... >

Re: [GENERAL] Querying a policy

Jeff, * Jean-Francois Bernier (jean.francois.bern...@boreal-is.com) wrote: > We are evaluating migrating our software RLS to Postgres by using policies. Neat! > Having a "FOR UPDATE POLICY" on a table, I was wondering if there is a way to > know, before trying an Update and getting an error,

Re: [GENERAL] Large data and slow queries

Greetings, * Samuel Williams (space.ship.travel...@gmail.com) wrote: > Thanks for all the suggestions Stephen. > > > That explain analyze shows a whole ton of heap fetches. When was the > last time a VACUUM was run on this table, to build the visibility map? > Without the visibility map being

Re: [GENERAL] Large data and slow queries

Greetings, * Samuel Williams (space.ship.travel...@gmail.com) wrote: > We want the following kinds of query to be fast: "kinds of query" isn't helpful, you should be reviewing exactly the queries you care about because statistics and your exact data set and what the exact query you're running is

Re: [GENERAL] A change in the Debian install

Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > (But ... these statements are based on an assumption of out-of-the- > box Postgres behavior. I would not exactly put it past the Debian > packagers to have decided to change this for reasons of their own, > and their track record of telling us about

Re: [GENERAL] browser interface to forums please?

Greetings, * Robin St.Clair (ro...@robinstclair.net) wrote: > Please do not encrypt what does not need to be encrypted. Signing > communications to a mailing list probably isn't required? Signing communications demonstrates that the message was, indeed, from me. You are certainly welcome to

Re: [GENERAL] browser interface to forums please?

Greetings Vinny, * vinny (vi...@xs4all.nl) wrote: > I meant it as "in an ideal world". It's a bit like buying a car > and finding out that they have not put the wheels on. It's not > difficult to put them on yourself, > but you kind of expect that the people who want you to user their > car would

Re: [GENERAL] browser interface to forums please?

Greetings, * vinny (vi...@xs4all.nl) wrote: > The thing is; mailinglists are far from userfiendly if you are not > used to them. > Even in this thread several people have explained how much work they > have done to get it > into a state where they can easily work with it. Can you expect Joe >

Re: [GENERAL] Checksum and Wal files

Greetings, * Poul Kristensen (bcc5...@gmail.com) wrote: > Are all files inclusive wal files added a checksum? WAL files include checksums, yes. The heap files in recent versions can also include checksums, but they don't by defualt. Currently, checksums can only be enabled at initdb-time,

Re: [GENERAL] Incremental / Level -1 backup in PG

Greetings, * rakeshkumar464 (rakeshkumar...@outlook.com) wrote: > >The short answer is 'no'. There are complications around this, > >particularly at the edges and because files can be written and rewritten > >as you're reading them. > >Basically, no file with a timestamp after the >

Re: [GENERAL] Incremental / Level -1 backup in PG

Greetings, * rakeshkumar464 (rakeshkumar...@outlook.com) wrote: > If first choice is lot faster in Oracle,DB2, I have reasons to believe that > the same should be true for PG also. But as someone explained, the PG > technology can not support this. This statement isn't correct. There are, in

Re: [GENERAL] Incremental / Level -1 backup in PG

John, * John R Pierce (pie...@hogranch.com) wrote: > On 3/21/2017 5:27 PM, Rakesh Kumar wrote: > >PG does not have a concept of incremental backup. The way it works in > >Oracle and other RDBMS is that incremental backup only backups up changed > >blocks since the last full backup. So if only

Re: [GENERAL] Incremental / Level -1 backup in PG

Greetings, * Rakesh Kumar (rakeshkumar...@outlook.com) wrote: > PG does not have a concept of incremental backup. The way it works in Oracle > and other RDBMS is that incremental backup only backups up changed blocks > since the last full backup. So if only 10% of blocks changed since the

Re: [GENERAL] Postgres backup solution

Lawrence, First off, I strongly recommend that you figure out how to send regular plain-text emails, at least to this mailing list, as the whole "winmail.dat" thing is going to throw people off and you're unlikely to get many responses because of it. Regarding your question.. * Lawrence Cohan

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Greetings, * Stephen Frost (sfr...@snowman.net) wrote: > * Frank van Vugt (ftm.van.v...@foxi.nl) wrote: > > Well, I didn't run into this issue with any of my db's that 'nicely' use > > tables in various schema's, it was actually the one 'older' db with > > everything >

Re: R: R: [GENERAL] Slow queries on very big (and partitioned) table

Greetings, * Job (j...@colliniconsulting.it) wrote: > >If this is really what you're mostly doing, having constraint exclusion and > >an index on 'profile' would probably be enough, if you insist on continuing > >to have the table partitioned by day (which I continue to argue is a bad > >idea-

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

Greetings, * Job (j...@colliniconsulting.it) wrote: > here is primary a partitioned table (for 20/2/2017 logs): > flashstart=# \d webtraffic_archive_day_2017_02_20; > Table > "public.webtraffic_archive_day_2017_02_20" > Column |Type

Re: [GENERAL] Slow queries on very big (and partitioned) table

Greetings, * Job (j...@colliniconsulting.it) wrote: > we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a > table. > Table is partitioned by day, with indexes on partitioned table. You probably shouldn't be partitioning by day for such a small dataset, unless you've

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Frank, * Frank van Vugt (ftm.van.v...@foxi.nl) wrote: > Well, I didn't run into this issue with any of my db's that 'nicely' use > tables in various schema's, it was actually the one 'older' db with > everything > in the public schema that brought it up, so maybe keeping one of those around >

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 02/13/2017 06:04 AM, Stephen Frost wrote: > >* Adrian Klaver (adrian.kla...@aklaver.com) wrote: > >>I am following this up to the point of not understanding what > >>exactly changed between 9.5 and 9

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Frank, * Frank van Vugt (ftm.van.v...@foxi.nl) wrote: > Op zaterdag 11 februari 2017 15:28:55 schreef Tom Lane: > > I'm inclined to argue that it was a mistake to include any non-pinned > > objects in pg_init_privs. > > > We might need to fix pg_dump too, but I think these entries in > >

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Adrian, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > I am following this up to the point of not understanding what > exactly changed between 9.5 and 9.6. Namely 9.5 does include the > default ACL's in the dump output and 9.6 does not. Quite a bit in pg_dump changed, but the relevant bit

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost <sfr...@snowman.net> writes: > > I'm not seeing a very simple answer for this, unfortunately. > > I'm inclined to argue that it was a mistake to include any non-pinned > objects in pg_init_privs. The reason in

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Greetings, * Frank van Vugt (ftm.van.v...@foxi.nl) wrote: > I noticed the following and wondered whether this is intentional or an > oversight in pg_dump's '-c' option? > > The clean option causes the public schema to be dropped and recreated, but > this is done with the default schema

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

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

Re: R: [GENERAL] Partitioned "views"

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] Partitioned "views"

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

Re: [GENERAL] Doubts regarding postgres Security

* 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] Doubts regarding postgres Security

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

Re: [GENERAL] Doubts regarding postgres Security

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

Re: [GENERAL] PgPool or alternatives

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

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

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

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

* 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

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

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] Is there a reason the "-v/verbose" switch is not printed with pg_dumpall --help

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] Querying dead rows

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

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

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

Re: [GENERAL] Recursive row level security policy

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: [GENERAL] Postgresql 9.6 and Big Data

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] Row level security performance joining large tables

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] avoiding index on incremental column

* 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] BRIN indexes and ORDER BY

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

Re: [GENERAL] Multi tenancy : schema vs databases

* 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

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

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

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

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

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

* 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

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

* 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

Re: [GENERAL] Uber migrated from Postgres to MySQL

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

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

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

Re: [GENERAL] Memory usage per session

* 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

Re: [GENERAL] Fastest memmove in C

* 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

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

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

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

* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost <sfr...@snowman.net> wrote: > > * Vik Fearing (v...@2ndquadrant.fr) wrote: > >> On 03/06/16 04:32, Michael Paquier wrote: > >> > On Fri, Jun 3, 2016

Re: [GENERAL] postgres_fdw and Kerberos authentication

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

* 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 <sameer.ku...@ashnik.com> > > wrote: > >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost <sfr...@snowman.net> wrote: > &g

Re: [GENERAL] psql remote shell command

* 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

Re: [GENERAL] psql remote shell command

* 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

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

David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost <sfr...@snowman.net> wrote: > > > * Sameer Kumar (sameer.ku...@ashnik.com) wrote: > > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a.igna...@postgrespr

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

* 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

Re: [GENERAL] postgres_fdw and Kerberos authentication

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

Re: [GENERAL] Clarify "allow_system_table_mods"

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

Re: [GENERAL] Clarify "allow_system_table_mods"

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

Re: [GENERAL] Clarify "allow_system_table_mods"

* 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

Re: [GENERAL] Clarify "allow_system_table_mods"

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

Re: [GENERAL] Enhancement Request

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

Re: [GENERAL] Postgresql 9.3.4 file system compatibility

* 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

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

* 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

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

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] Only owners can ANALYZE tables...seems overly restrictive

David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Mar 24, 2016 at 4:51 AM, Stephen Frost <sfr...@snowman.net> 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 VACU

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

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

Re: [GENERAL] UPSERT and HOT-update

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,

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

* David G. Johnston (david.g.johns...@gmail.com) wrote: > On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfr...@snowman.net> wrote: > > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > > Given the amount of damage a person with write access to a table

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

* 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

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

* Adam Guthrie (asguth...@gmail.com) wrote: > On 24 February 2016 at 20:27, Stephen Frost <sfr...@snowman.net> 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 > &g

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

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)

  1   2   3   4   5   >