Re: [GENERAL] BDR: cannot remove node from group
On 2015-09-15 16:45, Florin Andrei wrote: On 2015-08-25 18:29, Craig Ringer wrote: On 26 August 2015 at 07:19, Florin Andrei wrote: What do I need to do to start over? I want to delete all traces of the BDR configuration I've done so far. you need to DROP the database you removed, then re-create it as a new empty database. You cannot re-join a node that has been removed. postgres=# DROP DATABASE bdrdemo; ERROR: database "bdrdemo" is being accessed by other users DETAIL: There is 1 other session using the database. Something's holding it open, not sure exactly what. More specifically, it seems like it's the bgworker that's holding that DB: 1123 ?S 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf 1124 ?Ss 0:00 \_ postgres: logger process 1126 ?Ss 0:00 \_ postgres: checkpointer process 1127 ?Ss 0:00 \_ postgres: writer process 1128 ?Ss 0:00 \_ postgres: wal writer process 1129 ?Ss 0:00 \_ postgres: autovacuum launcher process 1130 ?Ss 0:00 \_ postgres: stats collector process 1136 ?Ss 0:00 \_ postgres: bgworker: bdr supervisor 1137 ?Ss 0:00 \_ postgres: bgworker: bdr db: bdrdemo Should I just kill that process, or is there a "nicer" way to do it? More generally, is there a way to just turn off BDR entirely on one node? I can't find a clear answer in the documentation to questions like - how do I turn on or off replication altogether? -- Florin Andrei http://florin.myip.org/ -- 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: cannot remove node from group
On 2015-08-25 18:29, Craig Ringer wrote: On 26 August 2015 at 07:19, Florin Andrei wrote: What do I need to do to start over? I want to delete all traces of the BDR configuration I've done so far. you need to DROP the database you removed, then re-create it as a new empty database. You cannot re-join a node that has been removed. postgres=# DROP DATABASE bdrdemo; ERROR: database "bdrdemo" is being accessed by other users DETAIL: There is 1 other session using the database. Something's holding it open, not sure exactly what. -- Florin Andrei http://florin.myip.org/ -- 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
I still do not see any problem. The whole purpose of the function is to copy ALL sequences , tables and functions to "new" schema, so new.old WILL exist. I don't see how you can possibly write a function that references a schema that does not yet exist! Again, please provide a _working_ example of what you think the problem is. Melvin, This statement: SELECT old.field FROM old.old; selects column “field” from table “old” which is in schema “old”. Your script converts it into: SELECT new.field FROM new.old which will try to select column “field” from table “new” in schema “new”. The obvious problem is that there is no table “new” in schema “new”, the table will still be called “old”. Jim’s example is very similar to what I provided a few days ago. Regards, Igor Neyman
Re: [GENERAL] clone_schema function
That is correct. But table old will NOT be converted to new because only the schema name is converted. And table "old" WILL exist because it will also be copied. I have tested and it works properly. Please do not provide hypothetical examples. Give me an actual working example that causes the problem. This statement: SELECT old.field FROM old.old; selects column “field” from table “old” which is in schema “old”. Your script converts it into: SELECT new.field FROM new.old which will try to select column “field” from table “old” in schema “new”. Again: SELECT new.field means select column “field” from table “new”, which does not exists. Not sure, what other example you need. Regards, Igor Neyman
Re: [GENERAL] [BUGS] BUG #13619: regression functions return Null
Oops!! My bad.. I did a reply and not a reply all... Anyways Thanks David.. I knew this wasn't really a bug but could not figure out where to post my questions. Let me just try the same in a different way or do it differently as you explained and see if I can make it to work. Thanks On Tue, Sep 15, 2015 at 12:33 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > 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. >> >> >> > > -- Best Regards, Biswadeep Banerjee
Re: [GENERAL] BDR truncate and replication sets
Sylvain MARECHAL wrote:
> Hello all,
>
> To avoid replication of some tables, I use a specific replication set.
> For example, with 2 nodes 'node1' and 'node2' and a table 'test' which
> content shall not be replicated, I do the following:
>
> mydb=# CREATE TABLE test (i INT PRIMARY KEY NOT NULL);
> mydb=# SELECT bdr.table_set_replication_sets('test', ARRAY['test_rep_set']);
>
> After that, adding (INSERT) or removing (DELETE) data in 'node1' or 'node2'
> can be done independently.
> The exception is with TRUNCATE: In case it is called, data is removed on
> both nodes.
>
> Is it a feature or a bug?
I think it's an oversight. Replication sets were added later than the
TRUNCATE trigger, so the design for the latter does not consider the
former as far as I know.
> (I am not sure it is related with
> https://github.com/2ndQuadrant/bdr/issues/93)
Doesn't look related.
> Is there a workaround?
> (deleting the truncate trigger seems to work, but I am sure it is safe to do
> it)
Well, the truncate trigger is there to replicate the truncate to other
servers. If you don't want truncate to be propagated, dropping the
trigger is one way to achieve that effect.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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
David, Yes, it would be nice, but 1. I am still working also on bringing over the comments for various objects 2. What you request is currently beyond my capability. Not to mention that there already are existing tools that do that, albeit they are not free. On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > To make the casual user's life easier, in the face of this reality, it > would nice if the routine would generate a reasonably attempted "diff" > between the two so that all changes can be reviewed in a structured manner > aided by correctly configured tools and advice. > > On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson > wrote: > >> Igor, >> I understand your point, however, I have spent over a week making a >> function >> that previously did very little do a lot. >> Naming a table the same as a schema is a very silly idea. >> >> Unless you care to take the time to provide a full >> schema, and function that fails for reasonable , practical design >> I will ignore all further comments. >> >> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman >> wrote: >> >>> >>> >>> That is correct. But table old will NOT be converted to new because >>> >>> only the schema name is converted. And table "old" WILL exist because it >>> will also be copied. >>> >>> I have tested and it works properly. >>> >>> Please do not provide hypothetical examples. Give me an actual working >>> example that causes the problem. >>> >>> This statement: >>> >>> SELECT old.field FROM old.old; >>> >>> selects column “field” from table “old” which is in schema “old”. >>> >>> Your script converts it into: >>> >>> SELECT new.field FROM new.old >>> >>> which will try to select column “field” from table “old” in schema >>> “new”. >>> >>> >>> >>> Again: >>> >>> SELECT new.field >>> >>> means select column “field” from table “new”, which does not exists. >>> >>> Not sure, what other example you need. >>> >>> Regards, >>> >>> Igor Neyman >>> >>> >> >> >> -- >> *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.
Re: [GENERAL] clone_schema function
To make the casual user's life easier, in the face of this reality, it would nice if the routine would generate a reasonably attempted "diff" between the two so that all changes can be reviewed in a structured manner aided by correctly configured tools and advice. On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson wrote: > Igor, > I understand your point, however, I have spent over a week making a > function > that previously did very little do a lot. > Naming a table the same as a schema is a very silly idea. > > Unless you care to take the time to provide a full > schema, and function that fails for reasonable , practical design > I will ignore all further comments. > > On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman > wrote: > >> >> >> That is correct. But table old will NOT be converted to new because >> >> only the schema name is converted. And table "old" WILL exist because it >> will also be copied. >> >> I have tested and it works properly. >> >> Please do not provide hypothetical examples. Give me an actual working >> example that causes the problem. >> >> This statement: >> >> SELECT old.field FROM old.old; >> >> selects column “field” from table “old” which is in schema “old”. >> >> Your script converts it into: >> >> SELECT new.field FROM new.old >> >> which will try to select column “field” from table “old” in schema “new”. >> >> >> >> Again: >> >> SELECT new.field >> >> means select column “field” from table “new”, which does not exists. >> >> Not sure, what other example you need. >> >> Regards, >> >> Igor Neyman >> >> > > > -- > *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
Igor, I understand your point, however, I have spent over a week making a function that previously did very little do a lot. Naming a table the same as a schema is a very silly idea. Unless you care to take the time to provide a full schema, and function that fails for reasonable , practical design I will ignore all further comments. On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman wrote: > > > That is correct. But table old will NOT be converted to new because > > only the schema name is converted. And table "old" WILL exist because it > will also be copied. > > I have tested and it works properly. > > Please do not provide hypothetical examples. Give me an actual working > example that causes the problem. > > This statement: > > SELECT old.field FROM old.old; > > selects column “field” from table “old” which is in schema “old”. > > Your script converts it into: > > SELECT new.field FROM new.old > > which will try to select column “field” from table “old” in schema “new”. > > > > Again: > > SELECT new.field > > means select column “field” from table “new”, which does not exists. > > Not sure, what other example you need. > > Regards, > > Igor Neyman > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] pgpass (in)flexibility
On Sep 15, 2015, at 12:27 AM, Jim Nasby wrote:
>
> On 9/15/15 12:48 AM, Ben Chobot wrote:
>> 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.
>
> I don't know of a way around that, but you might be better off using SSL
> certs to authenticate. I believe there's even something similar to
> ssh-keychain that would allow you not to store the passphrase on-disk (though
> you would have to enter it manually on reboot).
Does that solve the "different passwords for different servers" problem, or
just the "password on disk" problem?
--
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] Exclusively locking parent tables while disinheriting children.
On 7 August 2015 at 12:34, Thom Brown wrote:
>
> On 30 July 2015 at 13:35, Rowan Collins wrote:
>
>> Hi,
>>
>> When working with partition sets, we're seeing occasional errors of
>> "could not find inherited attribute..." in Select queries. This is
>> apparently caused when an "ALTER TABLE ... NO INHERIT" runs concurrently
>> with another transaction selecting from the relevant child table.
>>
>> I found an old bug report filed against 8.3 back in 2008 [1] I can still
>> reproduce the test case in that report on 9.1.11 and 9.3.5, and it seems to
>> match what we're seeing in production.
>>
>> Tom Lane said at the time that a lock would cause more problems than it
>> solved [2], but when I add an explicit lock statement ("LOCK TABLE ONLY
>> p_tbl, c1_tbl IN ACCESS EXCLUSIVE MODE;") between "BEGIN" and "ALTER
>> TABLE", I get the behaviour I would expect - the SELECT blocks until the
>> transaction is committed, then returns rows from the remaining child table.
>>
>> So what I want to understand is what the risk of adding this lock are -
>> under what circumstances would I expect to see dead locks if I manually
>> added this lock to my partition maintenance functions?
>>
>
> I'm not clear on the problems such a change would present either, but I'm
> probably overlooking the relevant scenario.
>
Has anyone got insight as to what's wrong with exclusively locking a parent
table to disinherit a child table?
Thom
Re: [GENERAL] clone_schema function
That is correct. But table old will NOT be converted to new because only the schema name is converted. And table "old" WILL exist because it will also be copied. I have tested and it works properly. Please do not provide hypothetical examples. Give me an actual working example that causes the problem. On Tue, Sep 15, 2015 at 9:39 AM, Igor Neyman wrote: > I still do not see any problem. The whole purpose of the function is to > copy ALL sequences , tables and functions to "new" schema, so new.old WILL > exist. > > > I don't see how you can possibly write a function that references a schema > that does not yet exist! > > Again, please provide a _working_ example of what you think the problem is. > > > > Melvin, > > > > This statement: > > > > SELECT old.field FROM old.old; > > > > selects column “field” from table “old” which is in schema “old”. > > > > Your script converts it into: > > > > SELECT new.field FROM new.old > > > > which will try to select column “field” from table “new” in schema “new”. > > The obvious problem is that there is no table “new” in schema “new”, the > table will still be called “old”. > > > > Jim’s example is very similar to what I provided a few days ago. > > > > Regards, > > Igor Neyman > > > -- *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
I still do not see any problem. The whole purpose of the function is to copy ALL sequences , tables and functions to "new" schema, so new.old WILL exist. I don't see how you can possibly write a function that references a schema that does not yet exist! Again, please provide a _working_ example of what you think the problem is. On Tue, Sep 15, 2015 at 3:22 AM, Jim Nasby wrote: > On 9/14/15 8:02 PM, Melvin Davidson wrote: > >> 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. >> > > Read my example again: > > SELECT old.field FROM old.old; > > That will end up as > > SELECT new.field FROM new.old > > Which will give you this error: > > ERROR: missing FROM-clause entry for table "new" > LINE 1: SELECT new.field FROM new.old; > > Even if you could fix that, there's yet more problems you'll run into, > like if someone has a plpgsql block with the same name as the old schema. > > I'm not trying to denigrate the work you and others have put into this > script, but everyone should be aware that it's impossible to create a > robust solution without a parser. Unfortunately, you could end up with a > function that still compiles but does something rather different after the > move. That makes the script potentially dangerous (granted, the odds of > this are pretty low). > > One thing I think would be very interesting is a parser that preserves > whitespace and comments. That would allow us to store a parsed version of > (at least plpgsql and sql) functions. The same technique would also be > handy for views. This would allow a lot (all?) other renames to propagate > to functions instead of breaking them (as currently happens). > > Another option is supporting some kind of official way to specially > designate database objects in any procedure language (ie, the @schema@ > syntax that extensions use). That would make it possible to rename properly > written functions without adverse side effects. > > -- > 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.
[GENERAL] Multiple Update queries
Hello, I am using a foreign data wrapper where i get a portion of my data pre-loaded , i.e I get a set of rows before hand . So now i want to run multiple update queries on this loaded data , write the changes to file , load the next set and continue with updates again. How should i try to achieve my requirement ?? At what point in postgres should i use the hook and change my execution ?? Thanks, Harsha
[GENERAL] BDR truncate and replication sets
Hello all,
To avoid replication of some tables, I use a specific replication set.
For example, with 2 nodes 'node1' and 'node2' and a table 'test' which
content shall not be replicated, I do the following:
mydb=# CREATE TABLE test (i INT PRIMARY KEY NOT NULL);
mydb=# SELECT bdr.table_set_replication_sets('test', ARRAY['test_rep_set']);
After that, adding (INSERT) or removing (DELETE) data in 'node1' or
'node2' can be done independently.
The exception is with TRUNCATE: In case it is called, data is removed on
both nodes.
Is it a feature or a bug?
(I am not sure it is related with
https://github.com/2ndQuadrant/bdr/issues/93)
Is there a workaround?
(deleting the truncate trigger seems to work, but I am sure it is safe
to do it)
Thanks and Regards,
--
Sylvain
--
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] Materialized View or table?
On 9/15/15 2:14 AM, Johann Spies wrote: What are the pro's and con's of large materialized views vs. tables in a case like this? AFAIK a matview is essentially the same as a table under the covers, so I don't believe there's any reason not to use one. At some point we'll have incremental refresh of some sort, which might help in your case. -- 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] pgpass (in)flexibility
On 9/15/15 12:48 AM, Ben Chobot wrote:
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.
I don't know of a way around that, but you might be better off using SSL
certs to authenticate. I believe there's even something similar to
ssh-keychain that would allow you not to store the passphrase on-disk
(though you would have to enter it manually on reboot).
--
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
On 9/14/15 8:02 PM, Melvin Davidson wrote: 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. Read my example again: SELECT old.field FROM old.old; That will end up as SELECT new.field FROM new.old Which will give you this error: ERROR: missing FROM-clause entry for table "new" LINE 1: SELECT new.field FROM new.old; Even if you could fix that, there's yet more problems you'll run into, like if someone has a plpgsql block with the same name as the old schema. I'm not trying to denigrate the work you and others have put into this script, but everyone should be aware that it's impossible to create a robust solution without a parser. Unfortunately, you could end up with a function that still compiles but does something rather different after the move. That makes the script potentially dangerous (granted, the odds of this are pretty low). One thing I think would be very interesting is a parser that preserves whitespace and comments. That would allow us to store a parsed version of (at least plpgsql and sql) functions. The same technique would also be handy for views. This would allow a lot (all?) other renames to propagate to functions instead of breaking them (as currently happens). Another option is supporting some kind of official way to specially designate database objects in any procedure language (ie, the @schema@ syntax that extensions use). That would make it possible to rename properly written functions without adverse side effects. -- 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
[GENERAL] Materialized View or table?
I have a table (A) with 750+ million records and another one (B) which is a summary of information in A containing 30+million records. Now I wonder whether it wouldn't be better to have B as an indexed materialized view. Though not often, there will be situations where B has to be updated. In the past I avoided materialized views when the result of a query contains more than about 1 million records. I do not know enough about the implications for the server's resources to make an informed decision though. What are the pro's and con's of large materialized views vs. tables in a case like this? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [GENERAL] avoid lock conflict between SELECT and TRUNCATE
On 9/11/15 4:50 AM, Sridhar N Bamandlapally wrote: 1) creating temporary table (say temp_users) on table users with required data/columns-list and index on column user_id, ...this will be faster as there will be no joins with other tables 2) also need index on table auths_with_trans column user_id 3) replacing users with temp_users in BEGIN block That's not really going to help unless the slow part is that you're using a cursor (which is why I don't like them...) FWIW, now that there's better transactional visibility support in the catalogs it might be possible to reduce the lock contention of trunkcate. -- 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
