Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 28.02.2016 o 03:35, David G. Johnston pisze: > W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze: > > Can anybody suggest any other way out of this mass? > > > ​The only thought that sticks while reading your prose is:​ > > ​message > message-person < person​ > > >

Re: [GENERAL] Function fixing - PostgreSQL 9.2

On Mon, Feb 29, 2016 at 6:14 PM, drum.lu...@gmail.com wrote: > > > On 1 March 2016 at 11:35, David G. Johnston > wrote: > >> On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com < >> drum.lu...@gmail.com> wrote: >> >>> *Question:* >>> >>>

Re: [GENERAL] Function fixing - PostgreSQL 9.2

On 1 March 2016 at 11:35, David G. Johnston wrote: > On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com < > drum.lu...@gmail.com> wrote: > >> *Question:* >> >> Payments in a Pending state cannot be invoiced and are excluded from the >> Invoice Runs section, but

[GENERAL] Looking for pure C function APIs for server extension: language handler and SPI

What I need (to find or create) is a ‘pure’ C language API to support a Postgres server extension. By ‘pure’ I mean one that has no knowledge of Postgres internals and that could be called by a generic interface provided by some other tool that can support C language APIs. The reason is

Re: [GENERAL] ERROR: cannot convert relation containing dropped columns to view

I wrote: > Nicklas =?ISO-8859-1?Q?Av=E9n?= writes: >> ERROR: cannot convert relation containing dropped columns to view > When we made rules with RETURNING go through this logic, in 2006, we > don't seem to have revisited the message text, much less thought about >

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 Feb 2016 22:47, "Kevin Grittner" wrote: > > On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless wrote: > > > I'm not really sure what changes I could make that would make one > > index that's ostensibly equivalent to the other not be attractive to > >

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless wrote: > I'm not really sure what changes I could make that would make one > index that's ostensibly equivalent to the other not be attractive to > the planner though. I can mess with those figures but as I said before > the

Re: [GENERAL] Function fixing - PostgreSQL 9.2

On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com wrote: > *Question:* > > Payments in a Pending state cannot be invoiced and are excluded from the > Invoice Runs section, but they are showing in the count mechanic. > > How can I solve this? > ​In 9.2 you probably need

[GENERAL] Function fixing - PostgreSQL 9.2

There is a number beside each color flag, and a number in the "sub status" drop down. Instead of "the number of jobs that have billable items that can be invoiced, it shows the "the number of jobs that have "something" billable but not yet invoiced." And since those payments cannot be invoiced

[GENERAL] substring on bit(n) and bytea types is slow

Hi, Queries like this: SELECT substring(bitarray from (32 * (n - 1) + 1) for 32) -- bitarray is a column of type bit(6400) FROM array_test_bit JOIN generate_series(1, 1) n ON true; SELECT substring(bytearr from (8 * (n - 1) + 1) for 8) -- bytearr is a column of type bytea FROM

[GENERAL] Confusing with commit time usage in logical decoding

Dear postgresql general mailing list, I am currently using the logical decoding feature (version 9.6 I think as far as I found in the source, wal_level: logical, max_replication_slot: > 1, track_commit_timestamp: on, I am not sure whether this will help or not). Following the online

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

On Mon, Feb 29, 2016 at 11:50 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 29, 2016 at 10:39 AM, Tom Lane wrote: > >> "David G. Johnston" writes: >> > On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 February 2016 at 18:31, Joshua D. Drake wrote: > I haven't been following this thread but did you try looking at the costs? Thanks for the response... > #seq_page_cost = 1.0# measured on an arbitrary scale > #random_page_cost = 4.0

Re: [GENERAL] bloated postgres data folder, clean up

Rémi Cura wrote: > Hey dear list, > after a fex years of experiments and crash, > I ended up with a grossly bloated postgres folder. > I had about 8 Go of useless files. Would you add a new page to the wiki with this? https://wiki.postgresql.org/wiki/Category:Administrative_Snippets -- Álvaro

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

On Mon, Feb 29, 2016 at 10:39 AM, Tom Lane wrote: > "David G. Johnston" writes: > > On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz > > wrote: > >> See http://www.postgresql.org/docs/current/static/planner-stats.html > >>

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

On Mon, Feb 29, 2016 at 10:36 AM, Adrian Klaver wrote: > ​So the typical user doesn't know or even care that what they just did >> needs to be analyzed. The situation is no worse than it is today. But >> as someone who writes many scripts and applications to perform

Re: [GENERAL] bloated postgres data folder, clean up

> Hope this may be useful Thanks for sharing! Peter -- 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] Only owners can ANALYZE tables...seems overly restrictive

On Mon, Feb 29, 2016 at 10:35 AM, Joshua D. Drake wrote: > On 02/29/2016 09:09 AM, David G. Johnston wrote: > >> ​ >> Being able to run ANALYZE on a table in no way implies that ​I should be >> allowed to run ALTER TABLE SET STATISTICS on the same. >> ​ >> >> Only table

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 02/29/2016 10:05 AM, Geoff Winkless wrote: Just as a continuation of this, I can set effective_cache_size to 64MB and it will still use the single-column index, but PG flatly refuses to use the multicolumn index without effective_cache_size being an unfeasibly large number (2x the RAM in the

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

On 02/29/2016 10:06 AM, Alvaro Herrera wrote: Joshua D. Drake wrote: On 02/29/2016 05:31 AM, Stephen Frost wrote: Realistically, ANALYZE is a background/maintenance task that autovacuum should be handling for you. Incorrect. That would be autoanalyze and although they are similar they are

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

Joshua D. Drake wrote: > On 02/29/2016 05:31 AM, Stephen Frost wrote: > >Realistically, ANALYZE is a background/maintenance task that autovacuum > >should be handling for you. > > Incorrect. That would be autoanalyze and although they are similar they are > not the same. ANALYZE is used for a

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

Just as a continuation of this, I can set effective_cache_size to 64MB and it will still use the single-column index, but PG flatly refuses to use the multicolumn index without effective_cache_size being an unfeasibly large number (2x the RAM in the machine, in this case). Geoff -- Sent via

[GENERAL] bloated postgres data folder, clean up

Hey dear list, after a fex years of experiments and crash, I ended up with a grossly bloated postgres folder. I had about 8 Go of useless files. All is in a virtualbox, so I'm sure to be able to reproduce exactly, and fried my postgres folder a couple of time before getting it right. Julien

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

"David G. Johnston" writes: > On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz > wrote: >> See http://www.postgresql.org/docs/current/static/planner-stats.html >> "The amount of information stored in pg_statistic by ANALYZE, in >> particular the

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

On 02/29/2016 08:13 AM, David G. Johnston wrote: On Mon, Feb 29, 2016 at 8:28 AM, Stephen Frost >wrote: * David G. Johnston (david.g.johns...@gmail.com ) wrote: > On Mon, Feb 29, 2016 at 6:31 AM,

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

On 02/29/2016 09:09 AM, David G. Johnston wrote: ​ Being able to run ANALYZE on a table in no way implies that ​I should be allowed to run ALTER TABLE SET STATISTICS on the same. ​ Only table owners should be allowed to execute ALTER TABLE while, in my opinion, anyone with write capabilities on

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

On 02/29/16 06:20, Vik Fearing wrote: > > Hmm. How are you not the owner of a temporary table? After 'set session authorization ...' -- 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] Only owners can ANALYZE tables...seems overly restrictive

On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz wrote: > David G. Johnston wrote: > In a production environment you don't want a user to change your table > statistics. > > They could just set default_statistics_target to something stupid, > run

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

On 02/29/2016 05: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 it seems pointless to not allow them to analyze the table after their updates - since best practices would say that

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

David G. Johnston wrote: In a production environment you don't want a user to change your table statistics. They could just set default_statistics_target to something stupid, run ANALYZE and wreck the statistics for everyone. And then come back to the DBA and complain

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

On Mon, Feb 29, 2016 at 8:46 AM, Albe Laurenz wrote: > David G. Johnston wrote: > > On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz > wrote: > >> John R Pierce wrote: > >>> analyze has arguably fewer side effects, its a performance enhancement, >

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

On Mon, Feb 29, 2016 at 8:28 AM, Stephen Frost wrote: > * 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

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

David G. Johnston wrote: > On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz wrote: >> John R Pierce wrote: >>> analyze has arguably fewer side effects, its a performance enhancement, >>> its neither altering the schema or changing the data. >> In a production environment

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

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

On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz wrote: > John R Pierce wrote: > > On 2/28/2016 8:58 PM, Tom Lane wrote: > I should the check for whether a given user can or cannot analyze a > table > should be whether the user has INSERT, UPDATE, or DELETE

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

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 it seems pointless to not allow them to analyze the table after > their > >

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

2016-02-29 15:20 GMT+01:00 Vik Fearing : > On 02/29/2016 03:15 PM, Guillaume Lelarge wrote: > > 2016-02-29 14:31 GMT+01:00 Stephen Frost > >: > > > > Realistically, ANALYZE is a background/maintenance task that > autovacuum

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

On 02/29/2016 03:15 PM, Guillaume Lelarge wrote: > 2016-02-29 14:31 GMT+01:00 Stephen Frost >: > > Realistically, ANALYZE is a background/maintenance task that autovacuum > should be handling for you. > > Realistically, that can't happen

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 February 2016 at 14:07, Geoff Winkless wrote: > On 29 February 2016 at 14:06, Jim Mlodgenski wrote: >> No they are not the same. When you don't include a unit for >> effective_cache_size, it defaults to page size so you're saying 2146435072 * >> 8K >

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

2016-02-29 14:31 GMT+01:00 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 -

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On 29 February 2016 at 14:06, Jim Mlodgenski wrote: > No they are not the same. When you don't include a unit for > effective_cache_size, it defaults to page size so you're saying 2146435072 * > 8K Hah. Thanks Jim, like I said I was sure I'd be missing something :) Geoff

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

On Mon, Feb 29, 2016 at 8:56 AM, Geoff Winkless wrote: > I'm sure I'm missing something here. > > A query takes 50 seconds; it's doing a seq-scan on a joined table, > even though the table is joined via a field that's the leftmost column > in a multicolumn index >

[GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

I'm sure I'm missing something here. A query takes 50 seconds; it's doing a seq-scan on a joined table, even though the table is joined via a field that's the leftmost column in a multicolumn index (http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html says "equality constraints on

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] multiple UNIQUE indices for FK

W dniu 28.02.2016 o 03:35, David G. Johnston pisze: > W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze: > > Can anybody suggest any other way out of this mass? > > > ​The only thought that sticks while reading your prose is:​ > > ​message > message-person < person​ > > >

[GENERAL] Confusing with commit time usage in logical decoding

If you received this message twice, sorry for annoying since I did not subscribe successfully previously due to conflicting email domain. Dear postgresql general mailing list, I am currently using the logical decoding feature (version 9.6 I think as far as I found in the source, wal_level:

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

John R Pierce wrote: > On 2/28/2016 8:58 PM, Tom Lane wrote: I should the check for whether a given user can or cannot analyze a table should be whether the user has INSERT, UPDATE, or DELETE privileges. >> By that argument, we should allow anyone with any write access to do >>