Re: [GENERAL] GIN Trigram Index Size

[Christian Ramseyer]

On 10/09/15 06:40, Jeff Janes wrote:

> Vacuuming will allow the space to be reused internally.  It will not
> visibly shrink the index, but will mark that space as eligible for reuse.
> 
> If you have a 36GB index and a reindex would have reduced it to 15GB,
> then a vacuum will leave it at 36GB but with 21GB of that as free
> space.  The index should then stop growing and remain at the same size
> for 4 days while it fills up the internally freed space, at which point
> it would start growing again at its usual rate (until you did another
> vacuum).
> 

Hi Jeff

Thanks, I didn't think about that. I tried a manual analyze 4 days ago
(10.09.) when it was at 41 GB, and it stayed the same size since then,
so this works as expected.

> Your best bet for now might be to turn off fastupdate on that index.  It
> will eliminate the re-occurrence of the bloat, but might cause your
> insertions to become too slow (on the other hand, it might make them
> faster on average, it is hard to know without trying it).  If you can't
> turn it off, then you can set the table-specific
> autovacuum_analyze_scale_factor to a very small value (even zero) to get
> autoanalyze to process the table more often.
>  
> 

Yeah the default autovacuum settings are what allowed the index to go
unchecked to about 120 GB and fill our disk, I'll tune this for these
tables.

The data sometimes arrives in bursts and I'm a bit affraid of making the
inserts slower, but I'll see if I can do a benchmark of fastupdate vs.
nofastupdate and will post it here if I get to it.

Thanks for your help
Christian



-- 
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] Ubuntu installed postgresql password failure

[Jan de Visser]

On September 14, 2015 07:22:58 AM Martín Marqués 
wrote:
> I would recommend using psql's \password meta-
command instead of ALTER
> USER to change a password, as to avoid having the 
password stamped in
> the logs.

You learn something new every day :-)

jan

Re: [GENERAL] GIN Trigram Index Size

[Francisco Olarte]

Hi Christian:

On Mon, Sep 14, 2015 at 1:54 PM, Christian Ramseyer  wrote:
> I agree with your append-only and disposable partition approach, it
> would work a lot better. The idea with using a second schema for
> selective backups is great, I'll totally steal this :)

Feel free. Just remember if you use redirection trigers/rules for
insert into the partitions it may lead to problems ( I do not normally
have them as I either use a partition aware dedicated inserter or zap
the archived tables from rules, as I only insert for the current date,
maintenance updates are done directly in the partitions ).



Francisco Olarte.


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

[GENERAL] issue, dumping and restoring tables with table inheritance can alter column order

[Ingmar Brouns]

Hi,

Im running:

 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.9.2
20150212 (Red Hat 4.9.2-6), 64-bit

I make use of table inheritance. I've created a table, added a
child table, and after that added a column to the parent table
(example code below).

As you can see below the newly added column is listed last in the
child table. I would have expected column 'c' to come after
column 'a', but can imagine that you can argue that it is safest
to put it last as people may have code that depends on column
positions.

However, when I dump the schema using pg_dump, and then load the
resulting sql file, suddenly 'c' does follow 'a'. So restoring my
schema has changed my table's layout. I feel dumping and loading
should not alter column positions. Any thoughts?

create schema test_issue;
create table test_issue.foo ( a integer );
create table test_issue.bar ( b text ) inherits ( test_issue.foo );
alter table test_issue.foo add column c integer;

postgres=# \d test_issue.bar;
Table "test_issue.bar"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
 b  | text|
 c  | integer |
Inherits: test_issue.foo

]$ ~/bin/pg_dump -n test_issue > test_issue.sql;

postgres=# drop schema test_issue cascade;

]$ psql -f test_issue.sql;

postgres=# \d test_issue.bar;
Table "test_issue.bar"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
 c  | integer |
 b  | text|
Inherits: test_issue.foo


Ingmar

Re: [GENERAL] issue, dumping and restoring tables with table inheritance can alter column order

[David G. Johnston]

On Mon, Sep 14, 2015 at 11:11 AM, Ingmar Brouns  wrote:

>
> However, when I dump the schema using pg_dump, and then load the
> resulting sql file, suddenly 'c' does follow 'a'. So restoring my
> schema has changed my table's layout. I feel dumping and loading
> should not alter column positions. Any thoughts?
>
>
​Any or all of the following:​

1) Help the community implement the outstanding concepts surrounding the
separation and recording of separate data for physical and logical column
order.
2) Suggest, and/or implement, ways that the current behavior could be more
readily discovered and comprehended by users without having to discover it
by accident.
3) Understand the problem and mitigate its impact in your specific work.

David J.

​

Re: [GENERAL] Ubuntu installed postgresql password failure

[Jan de Visser]

On September 14, 2015 07:22:58 AM Martín Marqués 
wrote:
> I would recommend using psql's \password meta-
command instead of ALTER
> USER to change a password, as to avoid having the 
password stamped in
> the logs.

You learn something new every day :-)

jan

Re: [GENERAL] issue, dumping and restoring tables with table inheritance can alter column order

[Jim Nasby]

On 9/14/15 11:59 AM, David G. Johnston wrote:

On Mon, Sep 14, 2015 at 11:11 AM, Ingmar Brouns >wrote:


However, when I dump the schema using pg_dump, and then load the
resulting sql file, suddenly 'c' does follow 'a'. So restoring my
schema has changed my table's layout. I feel dumping and loading
should not alter column positions. Any thoughts?


​Any or all of the following:​

1) Help the community implement the outstanding concepts surrounding the
separation and recording of separate data for physical and logical
column order.
2) Suggest, and/or implement, ways that the current behavior could be
more readily discovered and comprehended by users without having to
discover it by accident.
3) Understand the problem and mitigate its impact in your specific work.


To elaborate... without looking at the code I'm pretty sure what's 
happening here is that pg_dump simply dumps the entire parent table, 
including the added column. In fact, it must be doing this because we 
don't have any way to track when a column is added after table creation.


pg_dump then spits out CREATE TABLE child(...) INHERITS(parent), and the 
database correctly puts all the parent fields first in the child.


I'm pretty certain that nothing here violates relational theory. It's 
another example of why SELECT * is a bad idea. Hence why you should do 
#3. (I've thought about adding a "chaos" setting where all row results 
get ordered by random(). That wouldn't help this case until we get #1 
though.)


There's basically 0 chance of this being changed until #1 is done. At 
that point I'd expect pg_dump to start working correctly here, but it's 
also possible that adding a field to a parent would no longer go to the 
end of the children.


#2 could be as simple as a change to the documentation. Patches (or even 
just re-written text) welcome.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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] BDR problem

[Martín Marqués]

El 14/09/15 a las 06:37, Craig Ringer escribió:
>
> Support is focused mainly on RHEL/CentOS/Fedora, but Debian/Ubuntu
> packages are also produced. We're a little behind at the moment and
> haven't got 0.9.2 packages out. I'll be pushing 0.9.3 soon and will
> produce 0.9.3 packages for Debian/Ubuntu as well as for
> Fedora/RHEL/CentOS.

We (well, actually mostly you ;)) have pushed 0.9.2 bdr packages in rpm
and deb format.

$ rpm -qa | grep bdr94-bdr
postgresql-bdr94-bdr-debuginfo-0.9.2-1_2ndQuadrant.el7.centos.x86_64
postgresql-bdr94-bdr-0.9.2-1_2ndQuadrant.el7.centos.x86_64

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] clone_schema function

[Jim Nasby]

On 9/12/15 9:38 AM, Daniel Verite wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not
>have schema names of one letter.
>They usually name a schema something practical, which totally avoids your
>nit picky exception.

That's confusing the example with the problem it shows.

Another example could be:
if the source schema is "public" and the function body contains
GRANT SELECT on sometable to public;
then this statement would be wrongly altered by replace().


Well, the new version actually fixes that. But you could still trip this 
up, certainly in the functions. IE:


CREATE FUNCTION ...
  SELECT old.field FROM old.old;

That will end up as

  SELECT new.field FROM new.old

which won't work.


My objection is not about some corner case: it's the general
idea of patching the entire body of a function without a fully-fledged
parser that is dead on arrival.


ISTM that's also the biggest blocker for allowing extensions that refer 
to other schemas to be relocatable. It would be interesting if we had 
some way to handle this inside function bodies, perhaps via something 
equivalent to @extschema@.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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] clone_schema function

[Melvin Davidson]

Jim,

Have you actually tried this, or is it just a theory? AFAIK, the function
will work because only the schema name is changed.. So please provide
a full working example of a function that fails and I will attempt a
solution.

On Mon, Sep 14, 2015 at 6:36 PM, Jim Nasby  wrote:

> On 9/12/15 9:38 AM, Daniel Verite wrote:
>
>> "seriously flawed" is a bit of a stretch. Most sane developers would not
>>> >have schema names of one letter.
>>> >They usually name a schema something practical, which totally avoids
>>> your
>>> >nit picky exception.
>>>
>> That's confusing the example with the problem it shows.
>>
>> Another example could be:
>> if the source schema is "public" and the function body contains
>> GRANT SELECT on sometable to public;
>> then this statement would be wrongly altered by replace().
>>
>
> Well, the new version actually fixes that. But you could still trip this
> up, certainly in the functions. IE:
>
> CREATE FUNCTION ...
>   SELECT old.field FROM old.old;
>
> That will end up as
>
>   SELECT new.field FROM new.old
>
> which won't work.
>
> My objection is not about some corner case: it's the general
>> idea of patching the entire body of a function without a fully-fledged
>> parser that is dead on arrival.
>>
>
> ISTM that's also the biggest blocker for allowing extensions that refer to
> other schemas to be relocatable. It would be interesting if we had some way
> to handle this inside function bodies, perhaps via something equivalent to
> @extschema@.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] clone_schema function

[Melvin Davidson]

Actually, on further thought, you example shows that it works correctly
because we do want all references to the old schema to be changed to the
new schema, since all copies of functions will now reside in the new
schema. Otherwise, there is no point of duplicating those functions.

On Mon, Sep 14, 2015 at 8:42 PM, Melvin Davidson 
wrote:

> Jim,
>
> Have you actually tried this, or is it just a theory? AFAIK, the function
> will work because only the schema name is changed.. So please provide
> a full working example of a function that fails and I will attempt a
> solution.
>
> On Mon, Sep 14, 2015 at 6:36 PM, Jim Nasby 
> wrote:
>
>> On 9/12/15 9:38 AM, Daniel Verite wrote:
>>
>>> "seriously flawed" is a bit of a stretch. Most sane developers would not
 >have schema names of one letter.
 >They usually name a schema something practical, which totally avoids
 your
 >nit picky exception.

>>> That's confusing the example with the problem it shows.
>>>
>>> Another example could be:
>>> if the source schema is "public" and the function body contains
>>> GRANT SELECT on sometable to public;
>>> then this statement would be wrongly altered by replace().
>>>
>>
>> Well, the new version actually fixes that. But you could still trip this
>> up, certainly in the functions. IE:
>>
>> CREATE FUNCTION ...
>>   SELECT old.field FROM old.old;
>>
>> That will end up as
>>
>>   SELECT new.field FROM new.old
>>
>> which won't work.
>>
>> My objection is not about some corner case: it's the general
>>> idea of patching the entire body of a function without a fully-fledged
>>> parser that is dead on arrival.
>>>
>>
>> ISTM that's also the biggest blocker for allowing extensions that refer
>> to other schemas to be relocatable. It would be interesting if we had some
>> way to handle this inside function bodies, perhaps via something equivalent
>> to @extschema@.
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>> Experts in Analytics, Data Architecture and PostgreSQL
>> Data in Trouble? Get it in Treble! http://BlueTreble.com
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] pgpass (in)flexibility

[Ben Chobot]

We're in a situation where we would like to take advantage of the pgpass 
hostname field to determine which password gets used. For example:

psql -h prod-server -d foo # should use the prod password
psql -h beta-server -d foo # should use the beta password

This would *seem* to be simple, just put "prod-server" or "beta-server" into 
the hostname field of .pgpass. But if somebody uses the FQDN of those hosts, 
then the line does not match. If somebody uses the IP address of those hosts, 
again, no match. It seems that the hostname must match the hostname *exactly* - 
or match any host ("*"), which does not work for our use case.

This seems to make the hostname field unnecessarily inflexible. Has anybody 
else experienced - and hopefully overcome - this pain? Maybe I'm just going 
about it all wrong.

-- 
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] BDR problem

[Giovanni Maruzzelli]

http://bdr-project.org/docs/next/index.html

On Fri, Sep 11, 2015 at 11:21 PM, Charles Lynch <
charleslynchpostgre...@gmail.com> wrote:

> So for about a month now, we've been getting things prepared to use a BDR
> cluster in a production, multi-region setup on aws. Our initial testing
> produced some absolutely fantastic results with replication delays less
> than 150ms between singapore, ireland, and north virginia and this is will
> SSL encryption.
>
> We have, just recently, ran into a problem. I created a test cluster only
> within NV and after about a week of working without any problems, we got an
> error: Unexpected EOF on SSL connection. I had seen something like this
> before but on initial cluster join and chalked it up to me doing something
> wrong. This was after a week of working without issue. I wasn't sure what
> to do next. restarting the database started producing errors like this:
>
> LOG:  starting background worker process "bdr
> (6188205071755053119,1,16385,)->bdr (6188203625564571611,1,"
> FATAL:  mismatch in worker state, got 3, expected 1
> LOG:  starting background worker process "bdr
> (6188205071755053119,1,16385,)->bdr (6188203625564571611,1,"
> FATAL:  mismatch in worker state, got 3, expected 1
> FATAL:  mismatch in worker state, got 3, expected 1
> LOG:  starting background worker process "bdr
> (6188205071755053119,1,16385,)->bdr (6188203625564571611,1,"
> LOG:  worker process: bdr (6188205071755053119,1,16385,)->bdr
> (6188203625564571611,1, (PID 20300) exited with exit code 1
>
> This would repeat. So I removed this node from the cluster using the
> proper bdr commands and tried re-joining but that just resulted in the
> return error changing from a 3 to a 0 and the same errors repeating. I have
> BDR completely automated and orchestrated using chef so I simply fired up a
> new cluster and started over.
>
> My problem is I don't know what caused this and, more importantly, I'm not
> sure how to fix it / prevent it and I can't launch this into production
> without figuring this out.
>
> One other thing: I've seen a lot of conflicting information on how to
> setup BDR on ubuntu (using ppas, what pkg to install, and where to get
> source) I'm curious now if I don't have a younger version and that this
> issue is all but fixed now. Here are my build steps if anyone has any
> comments on how to setup bdr better, please let me know.
>
> I grab postgres 9.4.4 from here:
> https://github.com/2ndQuadrant/bdr/archive/bdr-pg/REL9_4_4-1.tar.gz
> and compile it with "./configure --prefix=/opt/psql --with-openssl && make
> -j4 -s install"
>
> then I compile and install the btree_gist module
>
> then I get the BDR plugin from here:
> https://github.com/2ndQuadrant/bdr/archive/bdr-plugin/0.9.2.tar.gz
> and compile it with "./configure && make -j4 -s all && make install"
>
> then init the db and set everything with config, ssl certs, and cluster
> creation and joining.
>
> Any help on this would be really appreciated.
>
> Thanks guys
>
> Charles
>



-- 
Sincerely,

Giovanni Maruzzelli
Cell : +39-347-2665618

Re: [GENERAL] [BUGS] BUG #13619: regression functions return Null

[David G. Johnston]

Oh, and don't reply to people individually.  I'm going to move this to
-general with one last copy for -bugs even though it is not one.

On Mon, Sep 14, 2015 at 3:01 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Sep 14, 2015 at 2:55 PM, Biswadeep Banerjee <
> biswadeep.baner...@actifio.com> wrote:
>
>> Hi David,
>> May be I gave a wrong example to explain my issue.
>>
>> Below is a sample query that I have been trying to get the slope and
>> intercept. The inner query basically gives me the time vs capacity, ie. x
>> and y data showing the actuals. Based on the x, y value, I calculate slope
>> and intercept value, as in the outer query.
>>
>> But it looks like am back to your comment as it mentions I am asking for
>> slope and intercept of a single point. Could you provide me some examples
>> that I can use as a reference.
>> As a reference, I am following similar example as in
>> http://stackoverflow.com/questions/20490756/linear-regression-with-postgres
>>
>> select x, y, regr_slope(y,x), regr_intercept(y,x)
>> from (
>> ​/* doesn't matter */
>>
>> ) i
>> group by x, y
>> order by x asc, y asc
>>
>
> ​You are grouping on (x, y)​
>
> ​ and then passing this single POINT into a function that requires
> multiple points in order to calculate the slope and intercept of a LINE
> (i.e., something requiring two points to describe) and are confused why it
> is giving you NULL...
>
> ​I have answered your question - you are using the functions incorrectly.
>
> This is operator error, not a bug.
>
> You have already found a reasonably good example of how these functions
> can be used.  Given I have never used them myself I do not have anything
> better to offer.
>
> David J.
> ​
> ​
>

Re: [GENERAL] BDR problem

[Craig Ringer]

On 12 September 2015 at 05:21, Charles Lynch
 wrote:

> We have, just recently, ran into a problem. I created a test cluster only
> within NV and after about a week of working without any problems, we got an
> error: Unexpected EOF on SSL connection. I had seen something like this
> before but on initial cluster join and chalked it up to me doing something
> wrong.

That's generally network level, though it could also occur if a worker
exits unexpectedly.

> This was after a week of working without issue. I wasn't sure what to
> do next. restarting the database started producing errors like this:
>
> LOG:  starting background worker process "bdr
> (6188205071755053119,1,16385,)->bdr (6188203625564571611,1,"
> FATAL:  mismatch in worker state, got 3, expected 1

That's ... very odd. It's violating a sanity check that shouldn't
really ever be triggered.

How exactly did you restart the database? Can you send more info on
your configuration via direct mail to me?

> This would repeat. So I removed this node from the cluster using the proper
> bdr commands and tried re-joining

You can't just re-join a removed node. Once it's removed it's removed
for ever. You have to drop the database (or re-initdb), create a new
blank database, and join it as a new node.

The reason for this is that when you remove the node the replication
slots on other nodes get dropped, so there's no record of what catchup
work needs to be done. It's not really possible to resync the node
with the rest after that. That's the point of node removal, to free
the resources from those slots when a node is retired, otherwise you'd
just switch it off.

> My problem is I don't know what caused this and, more importantly, I'm not
> sure how to fix it / prevent it and I can't launch this into production
> without figuring this out.

The "mismatch in worker state" is strongly likely to be a bug. The
trick will be figuring out how you triggered it.

Did you retain the malfunctioning cluster, or have you deleted it?

> One other thing: I've seen a lot of conflicting information on how to setup
> BDR on ubuntu (using ppas, what pkg to install, and where to get source) I'm
> curious now if I don't have a younger version and that this issue is all but
> fixed now. Here are my build steps if anyone has any comments on how to
> setup bdr better, please let me know.

You should use the apt respository referenced by
http://bdr-project.org/docs/stable/installation-packages.html#INSTALLATION-PACKAGES-DEBIAN
.

Support is focused mainly on RHEL/CentOS/Fedora, but Debian/Ubuntu
packages are also produced. We're a little behind at the moment and
haven't got 0.9.2 packages out. I'll be pushing 0.9.3 soon and will
produce 0.9.3 packages for Debian/Ubuntu as well as for
Fedora/RHEL/CentOS.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Ubuntu installed postgresql password failure

[Martín Marqués]

El 13/09/15 a las 18:20, Jan de Visser escribió:
> 
> Try
> 
> jan@bison:~$ sudo -u postgres -s
> postgres@bison:~$ psql
> psql (9.4.4)
> Type "help" for help.
> 
> postgres=# ALTER USER postgres PASSWORD 'postgres';
> ALTER ROLE
> postgres=# \q

I would recommend using psql's \password meta-command instead of ALTER
USER to change a password, as to avoid having the password stamped in
the logs.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


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