[GENERAL] 'distinct on' and 'order by' conflicts of interest

It has come up several times on the various postgresql lists that in order to get around the requirement of DISTINCT ON parameters matching the first ORDER BY parameters, wrap the distinct query in a new 'order by' query: select * from (select distinct on (a) a,b,c from foo order by a) order by c

Re: [GENERAL] 'distinct on' and 'order by' conflicts of interest

s | 802 | 4 | 0 * Upcoming | Public: Upcoming Exhibitions | 852 | 1 | 0 * Recent | Public: Recent Exhibitions | 870 | 2 | 0 * Hands-on History | Public: Hands-on History | 931 | 3 | 0 * thus destroying the 'ordinal' field&#

[GENERAL] VACUUM degrades performance significantly. Database becomes unusable!

colname28 varchar(10) DEFAULT '' NOT NULL, colname29varchar(10) DEFAULT 'jpeg' NOT NULL, colname30 varchar(20) DEFAULT '' NOT NULL, colname31 by

Re: [GENERAL] VACUUM degrades performance significantly. Database

QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=346) (actual time=1762.34..1762.37 rows=1 loops=1) Index Cond: (id = '3305b141837f065d673aa09cf382d331'::character varying) Total runtime: 1762.50 msec (3 rows) Regards,

Re: [GENERAL] embedded postgresql

PostgreSQL is not intended to be embedded into other programs. You might want to try SQLite, it's a free embeddable SQL engine: http://go.jitbot.com/sqlite regards "jini us" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I am starting a new project where I intend to use >

Re: [GENERAL] Partitioning and constraint exclusion

e offhand why not but it didn't work in my testing. Another approach to dealing with this is to use plpgsql functions and 'return execute' which essentially compute the constant and then build a dyanmic SQL query using the constant and return the results. It's a bit awkward com

Re: [GENERAL] Partitioning and constraint exclusion

Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Spam on main page

just missed that it also needed to be cleared from the main page. Thanks! Stephen signature.asc Description: Digital signature

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

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

ses will disappear across the upgrade.. I've not checked to see if something else in the pg_upgrade process will catch this, but if not, we should definitely add something. Thanks, Stephen signature.asc Description: Digital signature

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

template0 shouldn't have datallowconn as 'true'. That's why it's being included in the pg_dumpall. On your test setup, run (as superuser): update pg_database set datallowconn = false where datname = 'template0'; Then re-run the pg_upgrade. Thanks!

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

* 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

* 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

the replica) or you can create an independent database which has FDW tables to the replica. They have their own pros and cons, of course. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Strange security issue with Superuser access

* 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

Re: [GENERAL] Link Office Word form document with data from PostgreSQL

Open Office etc can populate fields via JDBC from any database including PostgreSQL. Cheers, Stephen On 30/03/15 19:52, Tim Clarke wrote: Two options that I know of: 1) Use Java and the Apache POI project 2) Write a file containing the data in some other format like HTML then open that with

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

at you *really* want to use timestamptz in PG for storing timestamps. > Also, is there any way to get the equiv of date +%s%N as a numeric or a > double precision? See above. Thanks! Stephen signature.asc Description: Digital signature

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

pecial when running in a NUMA environment, no, not at this time. Ditto with hyper-threading. > 7. does PostgreSQL support in-memory store (similar to Oracle 12c in-memory > > and SQL Server 2014 in-memory OLTP) ? > > > No. Temporary tables will be in memory unless they ove

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

ions/oracle-compatibility-technology That's certainly something to consider, but it's PPAS, not PostgreSQL, just to be clear. Thanks! Stephen signature.asc Description: Digital signature

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

* 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

snapshots and other technologies, you can make it happen quite quickly though. > >From my practice using a PostgreSQL for the terabyte scale and/or > mission-critical databases definitely possible but require very careful > design and planning (and good hardware). I'd argue that's true for any database of this type. :) Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] PostgreSQL OGC compliant

xtension to PostgreSQL. PostGIS follows the OpenGIS Simple Features Specification for SQL and is an excellent extension with a long history and which is maintained by an overlapping but independent community. http://www.postgis.org Thanks! Stephen signature.asc Descripti

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

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

nt, but using regular policies should also work. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Between with a list of ranges possible ?

Use UNION ALL: select * from table where number * 3 between start_value1 and end_value2 UNION ALL select * from table where number * 3 between start_value2 and end_value2 UNION ALL select * from table where number * 3 between start_value3 and end_value3; -- Stephen On 5/29/2015 12:32 PM

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

of that UPDATE must meet a different condition to be allowed to be added to the table. A simple case of this is "Joe can modify all records, but the result of that modification must update the last-modified-by column to be set to Joe." Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Creating a user for pg_start_backup

Note that the REPLICATION role gets a great deal more access than simply being able to run pg_start/stop_backup, such as being able to connect to the magic replication database and be able to stream the contents of the database. Would be great to understand your use-case better, to see if the proposed d

[GENERAL] Foreign Keys as first class citizens at design time?

ould look something like: SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN When building our databases we already put a lot of work in normalising as much as we can. Then after all that work we have to virtually start again building up select, insert and update statements etc. all with all that referential integrity in mind. The advantages of a first class foreign key field as I see it are at least two fold. One it make building and maintaining your database easier. Two it is a means to provide some iterative structures easily coded. To me this looks like a good idea. -- Kind regards Stephen Feyrer

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver wrote: On 08/13/2015 05:03 PM, Stephen Feyrer wrote: Hi, This is probably not an original question merely one which I haven't been able to find an answer for. Basically, the question is why is there not an equivalent foreign key conce

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On Fri, 14 Aug 2015 01:58:29 +0100, Adrian Klaver wrote: On 08/13/2015 05:40 PM, Stephen Feyrer wrote: On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver wrote: On 08/13/2015 05:03 PM, Stephen Feyrer wrote: Hi, This is probably not an original question merely one which I haven't

Re: [GENERAL] Foreign Keys as first class citizens at design time?

i, 14 Aug 2015 03:52:28 +0100, David G. Johnston wrote: On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer wrote: When we design databases, invariably, normally we design the queries at the same time. ​Well this may be true to an extent well implemented models have the ability to answe

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

ications are done inside of DO blocks and PL code. Further, it provides a great deal of additional flexibility. What we need here is better logging/auditing capabilities in core. Improving the documentation for the back-branches is good but doesn't address this use-case. It's unfortuna

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

files when they're saved, handles failure gracefully, etc, etc. Thanks! Stephen signature.asc Description: Digital signature

[GENERAL] Upgrade from 9.3 to 9.4 issue

s stocks template1 ok lc_collate cluster values do not match: old "en_US.UTF-8", new "en_AU.UTF-8" Failure, exiting How can I recover from here? Cheers and thanks, Stephen Davies -- Sent via pgsql-general mailing list

Re: [GENERAL] Upgrade from 9.3 to 9.4 issue

On 31/10/15 13:32, Tom Lane wrote: Stephen Davies writes: I have just upgraded from Fedora 21 to 22. This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster to fail because the existing databases are still at 9.3. As suggested, I then ran postgresql-setup --upgrade but

Re: [GENERAL] postgres_fdw and Kerberos authentication

iles (one for each backend which has authenticated via Kerberos and passed through delegation credentials) should work. Clearly, we can't give the user control over which credential cache to use. Having to trust the OS user and superusers with those credentials isn't any different from using passwords with postgres_fdw. Thanks! Stephen signature.asc Description: Digital signature

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

y either. Something along the lines of 'pg_xlog_file_list()', perhaps. There is a check in check_postgres.pl which could take advantage of this also. Should be a very straight-forward function to write, perhaps good as a starter project for someone. Thanks! Stephen signature.asc Description: Digital signature

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

an be updated via ALTER SYSTEM through psql also. Issuing a 'pg_ctl restart' via COPY PROGRAM isn't a good idea and may not work, though I suppose you could try if you really wish to. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] psql remote shell command

o have access to the postgres unix user account on the system, they shouldn't give you a PG superuser account. Thanks! Stephen signature.asc Description: Digital signature

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

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

* 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] PostgresSQL and HIPAA compliance

ew, it's the same as any other server running Linux (I can SSH in, or tunnel my DB connection). To be honest I'd rather have it this way than deal with the RDS interface. Try to avoid those HIPAA compliance meetings though, they are terrible and long. -- Stephen -- Sent via pgsql-ge

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

biased towards and prefer pgBackRest, as I helped start that project, but there are other tools, such as barman and WAL-E, which would still be better than trying to implement everything correctly on your own. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Fastest memmove in C

our own routines at times too. On the other hand, if there's a reason the glibc folks don't want this, we should consider that.. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Memory usage per session

an get "out of memory". > :((( Do you have 100 CPUs on this system which apparently doesn't have 16G of RAM available for PG to use? If not, you should probably consider connection pooling to reduce the number of PG sessions to something approaching the number of CPUs/cores you have in the system. Thanks! Stephen signature.asc Description: Digital signature

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

Re: [GENERAL] Uber migrated from Postgres to MySQL

new enough about lower version to > rejigger everything... just maybe it could do the reverse. That might work if you opened the database in read-only mode, but not once you start making changes. Thanks! Stephen signature.asc Description: Digital signature

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

t; is quite another. It's not an insurmountable problem, though it's a bit painful. Still, both the Debian-based and RedHat-based distributions demonstrate how it can be done. Thanks! Stephen signature.asc Description: Digital signature

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

h if you have a lot of WAL then that can still take a bit of time and disk space. With sufficient interest and resources, we might be able to make it happen, but I wouldn't expect it near-term. Until then, at least the database-level option, as David mentioned, can be used. Thanks! Stephen signature.asc Description: Digital signature

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

any effect. Note that if you revoke all privielges from 'public' then only users who have been explicitly granted access will be able to create or *use* any objects in the public schema. Generally, I revoke CREATE rights from the public schema, but leave USAGE rights, as I then put truste

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

rivileges column for the public schema, which shows that the 'postgres' role and the '' role (aka, 'public') have been granted both USAGE and CREATE on that schema. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Graphical entity relation model

On 29/09/16 05:47, jotpe wrote: Does anybody know a Software for generating graphical entity relation models from existing postgresql databases? Best regards Johannes I like SchemaSpy. -- = Stephen Davies Consulting

Re: [GENERAL] Multi tenancy : schema vs databases

an to try and move an individual schema or database. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] BRIN indexes and ORDER BY

s, where the rows are inserted in-order and never/very-rarely modified or deleted, this approach would work very well. Certainly, using this would be much cheaper than a seqscan/top-N sort, for small values of 'N', relative to the number of rows in the table, in those cases. In ge

[GENERAL] import_bytea function

. Is there a better way to update a bytea column from an uploaded file (in this case a small jpeg)? Cheers and thanks, Stephen -- 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] import_bytea function

On 07/10/16 18:48, Thomas Kellerer wrote: Stephen Davies schrieb am 07.10.2016 um 09:12: I am trying to use the import_bytea function described in various list posts (PG version 9.3.14) in a jsp. I get an error saying that only the super user can use server-side lo_import(). If I change the

Re: [GENERAL] import_bytea function

On 07/10/16 19:24, Thomas Kellerer wrote: Stephen Davies schrieb am 07.10.2016 um 10:46: You can store the contents of a file in a bytea using plain JDBC no lo_import() required String sql = "insert into images (id, image_data) values (?,?)"; Connection con = ; Fil

Re: [GENERAL] import_bytea function

On 07/10/16 19:24, Thomas Kellerer wrote: Stephen Davies schrieb am 07.10.2016 um 10:46: You can store the contents of a file in a bytea using plain JDBC no lo_import() required String sql = "insert into images (id, image_data) values (?,?)"; Connection con = ; Fil

Re: [GENERAL] import_bytea function (resolved)

On 08/10/16 17:16, Thomas Kellerer wrote: Stephen Davies schrieb am 08.10.2016 um 02:57: A follow-up question. Once the bytea column is populated, how best to display the content in a web page? I have : byte [] imgB; ResultSet rs = st1.executeQuery("select pic from part where pno=&

Re: [GENERAL] avoiding index on incremental column

o I'd like to avoid useless complex indexes if possible. A BRIN index should work pretty well in that scenario. A btree index would most likely be better/faster for query time, but more expensive to maintain. Thanks! Stephen signature.asc Description: Digital signature

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

ove it in the subsequent releases. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Postgresql 9.6 and Big Data

ncluded in that set, of course, and depending on your requirments you might wish to avoid updating them also. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Recursive row level security policy

uperuser to use the approach Joe recommended. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Er Data Modeller for PostgreSQL

On 23/12/16 16:20, Günce Kaya wrote: Hi All, I'm looking for an ER Data Modeller tool for postgresql. I use Navicat Premium for postgresql and the tool has a modeller but I would like to display a database modeller that belonging to a tables of an schema under a database. If I use Navicat for m

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

SQL database cluster into an SQL script file. Hmm. Looks like an oversight, will see about fixing it. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Querying dead rows

.6/static/pageinspect.html Thanks! Stephen signature.asc Description: Digital signature

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] LYDB: Feasible to use PG roles instead of application-level security?

can help is to use a common user for 'read-only/public-access (or at least low-value)' queries from the app, if there are such. > Is this practical? Has anyone here done it? What might the caveats be? Yes, yes, see above. Thanks! Stephen signature.asc Description: Digital signature

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

much of anything for connection-pooling scenarios. I don't agree that this is unsolvable, but it would require things like protocol-level changes which no one has had the gumption to work through and propose. In short, I agree with Guyren, there are features needed here that we don't have and it would be a great deal better if we did. Thanks! Stephen signature.asc Description: Digital signature

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

pure speculation, so feel free to ignore me if I'm completely off-base here. :) Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] PgPool or alternatives

7 feeding data constantly to PG, do you really need a connection pooler for those? Connection poolers make a lot of sense for environments where there's lots of down-time on the connection, but the less down-time, the less they make sense. Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Doubts regarding postgres Security

stgresql.org/docs/current/static/pgcrypto.html Thanks! Stephen signature.asc Description: Digital signature

Re: [GENERAL] Doubts regarding postgres Security

rtificate-based auth or GSSAPI. Thanks! Stephen signature.asc Description: Digital signature

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

approach which can be used is to have a trigger which will automatically update the side-table for every change to the 'big' table, but that will mean every update on the 'big' table takes longer and if the updates are happening concurrently then you may run into locking, and

Re: R: [GENERAL] Partitioned "views"

hey will be lost or remain intact? I was suggesting that you, essentially, write your own SQL to have a materialized view, *not* use the PG materialized view system. In other words, the 'side-table' that you create would be *your* materialized view, but to PG, it'd just look like

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

t there is often a bit of a learning curve and when others point things out that can sometimes be off-putting (not unlike our mailing lists..). In any case, I'm happy to try and help out if people feel that there's abusing of OPs or inappropriate behavior. Thanks! Stephen signature.asc Description: Digital signature

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

, and conceptually a view is just a query. The CURRENT_USER > issue is valid, but personally it's not too big for me as most auth is done > through database parameters. The hard part is making sure that what happens when there are policies on views actually makes sense and work

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

t is not wise to run with 'trust' in a non-development environment. Thanks! Stephen signature.asc Description: Digital signature

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

el policies is definitly on my list of things to look at doing, specifically to address these kinds of issues. That's not going to help you in the very short term though, unfortunately. Thanks! Stephen signature.asc Description: Digital signature

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

great. I don't quite see how saying "if the old and new value stay the same, then you can modify anything" makes sense- you have to consult some external source to determine if you're the owner of that row, right? Otherwise, anyone could change any row, provided that keep th

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

ferences to other tables through subqueries. You are correct that there are performance considerations, but those are essentially the same considerations you would have if the application was to perform the same joins and queries as part of the query, or if you were to include those in a view.

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

E/INSERT would be allowed is an interesting idea, it strikes me as being more complicated to explain and justify to users than any value it would add. Thanks! Stephen signature.asc Description: Digital signature

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

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

network > connection is broken and session left hanging. For psql prompt case looks > like pg_terminate_backend() would be the only solution. Those settings aren't for controlling idle timeout of a connection. pg_terminate_backend() will work and could be run out of a cronjob. Thanks! S

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

quired on the table, such as with an INSERT .. RETURNING. Thanks! Stephen signature.asc Description: Digital signature

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

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] WIP: CoC V2

ot;not limited to" part would allow us to castigate someone who is just really good at being a bully through the loopholes. And before someone says I'm the worst person ever for using the word "deviance", I meant like statistically (nobody ever complains about being in the

Re: [GENERAL] Replacement for Oracle Text

FWIW, I just use pdftotext in my CGI. -- ===== Stephen Davies Consulting P/L Phone: 08-8177 1595 Adelaide, South Australia.Mobile:040 304 0583 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] Replacement for Oracle Text

, but functional indexes also make that less important On Sat, Feb 20, 2016 at 1:10 AM, Stephen Davies mailto:sdav...@sdc.com.au>> wrote: On 20/02/16 00:24, Bruce Momjian wrote: On Fri, Feb 19, 2016 at 02:49:16PM +0100, s d wrote: On 19 February 2016 at 14:19, Bruce M

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

pact storage from a column-store database. > > There's ongoing investigation into extending Postgres to support > column-style storage for better support of applications like that; but any > such feature is probably several years away, and it will not come without > performance compromises of its own. One approach to dealing with the PG per-row overhead is to use arrays instead of rows, when you don't need that per-row visibility information. I've found that to be very successful for single-column tables, but the technique may work reasonably well for other structures also. Thanks! Stephen signature.asc Description: Digital signature

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

g an index scan with the btree and then filtering, but I do believe it to be a problem area that would be good to try and improve. The first question is probably- are we properly accounting for the cost of scanning the index vs the cost of scanning one index and then applying the filter? Thanks! Stephen signature.asc Description: Digital signature

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

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

;d really change what's going on here, though if it did, that would be interesting too. Thanks! Stephen signature.asc Description: Digital signature

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

measurable then perhaps we could avoid doing it for temp tables, but that strikes me as really the only case that it might be worthwhile and I'm not convinced it is. Thanks! Stephen signature.asc Description: Digital signature

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

* 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

isn't possible and that plan shouldn't be getting built/considered. As a work-around, until we fix it, you could create an sql function to check for the existance of the id in 'a' and use that in the policy definition. Thanks! Stephen signature.asc Description: Digital signature

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

before the 'SET ROLE', meaning that you're running it as the table owner, and the policy is ignored (policies are not applied to the owner of the table, unless FORCE RLS is used). Thanks! Stephen signature.asc Description: Digital signature

  1   2   3   4   5   6   7   >