Re: [GENERAL] Better way to process boolean query result in shell-like situations?

On 10/28/2015 09:42 PM, Tim Landscheidt wrote: Hi, I regularly run into the problem that I want to query a PostgreSQL database in a script/program and depending on a boolean result do one thing or the other. A typical example would be a Puppet Exec that creates a user only if it does not exist

Re: [GENERAL] Better way to process boolean query result in shell-like situations?

On 10/29/2015 06:07 AM, David wrote: On 10/28/2015 09:42 PM, Tim Landscheidt wrote: Hi, I regularly run into the problem that I want to query a PostgreSQL database in a script/program and depending on a boolean result do one thing or the other. A typical example would be a Puppet Exec that

Re: [GENERAL] How can I change defined schema of linked tables when using Access linked table manager odbc connection

On Oct 29, 2015, at 6:14 AM, Killian Driscoll wrote: > I am using postgresql 9.3 on Windows 8 64, and am using Access as a frontend > data entry. In postgresql I have changed the schema from 'public' to 'irll'. > The linked table in Access are still linked as 'public'

Re: [GENERAL] BDR-Plugin make install on RHEL7.1

On Thu, 2015-10-29 at 08:33 -0400, Will McCormick wrote: > Trying to get the bdr-plugin to install make install on RHEL7.1. Having some > issues with make of the plugin. > > > > # make -j4 -s all make -s install > make: *** No rule to make target `make'. Stop. > make: *** Waiting for

Re: [GENERAL] How can I change defined schema of linked tables when using Access linked table manager odbc connection

On 10/29/2015 05:00 AM, John J. Turner wrote: On Oct 29, 2015, at 6:14 AM, Killian Driscoll wrote: I am using postgresql 9.3 on Windows 8 64, and am using Access as a frontend data entry. In postgresql I have changed the schema from 'public' to 'irll'. The linked

[GENERAL] BDR-Plugin make install on RHEL7.1

Trying to get the bdr-plugin to install make install on RHEL7.1. Having some issues with make of the plugin. Here is what I have run verbatim and the error is at the very bottom. Any help is appreciated. Please let me know if there is another forum I should be posting this to. I'm assuming I'm

Re: [GENERAL] Better way to process boolean query result in shell-like situations?

On 10/29/2015 08:27 AM, Adrian Klaver wrote: On 10/29/2015 06:07 AM, David wrote: On 10/28/2015 09:42 PM, Tim Landscheidt wrote: Hi, I regularly run into the problem that I want to query a PostgreSQL database in a script/program and depending on a boolean result do one thing or the other. A

[GENERAL] mysql_fdw trouble

Hello, I have a MySQL/PHP app that I want to port to PostgreSQL so I just installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw because I'd like to do the data migration in SQL if possible. Installation and set up worked flawlessly but when I run the following query SELECT

Re: [GENERAL] mysql_fdw trouble

On 10/29/2015 10:47 AM, Dane Foster wrote: Hello, I have a MySQL/PHP app that I want to port to PostgreSQL so I just installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw because I'd like to do the data migration in SQL if possible. Installation and set up worked flawlessly

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 1:56 PM, Adrian Klaver wrote: > On 10/29/2015 10:47 AM, Dane Foster wrote: > >> Hello, >> >> I have a MySQL/PHP app that I want to port to PostgreSQL so I just >> installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw >> because

Re: [GENERAL] does pg_dump get delayed if I have a loop that does continuous insertions

>Probably not a good idea to put your data in the postgres database. This is one of the system databases created by >initdb and is meant to be used as a default database to connect to, not really as one to store data in. Just did a test >with pg_dumpall and it is not dumped when you backup the

Re: [GENERAL] does pg_dump get delayed if I have a loop that does continuous insertions

On 10/29/2015 10:54 AM, Melvin Davidson wrote: >Probably not a good idea to put your data in the postgres database. This is one of the system databases created by >initdb and is meant to be used as a default database to connect to, not really as one to store data in. Just did a test >with

[GENERAL] Domain check constraint not honored?

I have created a custom type as a domain based on text, which adds a check constraint using a regexp to limit it to containing digits and '.'. However I am finding I can add values with other characters to a column of this type. Is this to be expected for some reason? Or alternately, did I

Re: [GENERAL] mysql_fdw trouble

Dane Foster writes: > Installation and set up worked flawlessly but when I run the following query > ... > ​I get the following error:​ > ​ERROR: failed to prepare the MySQL query: > FUNCTION latest.btrim does not exist It looks like mysql_fdw is messing up by sending

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane wrote: > Dane Foster writes: > > Installation and set up worked flawlessly but when I run the following > query > > ... > > ​I get the following error:​ > > ​ERROR: failed to prepare the MySQL query: > >

Re: [GENERAL] mysql_fdw trouble

On 10/29/2015 11:20 AM, Dane Foster wrote: On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane > wrote: Dane Foster > writes: > Installation and set up worked flawlessly but when I run the following

Re: [GENERAL] Configure Different Databases on One Server

John R Pierce writes: > On 10/29/2015 11:52 AM, Jim Longwill wrote: >> I would like to do a different vacuum configuration on ddev2 than on >> ddev1, such as turning off autovacuum, etc. How do I do this? E.g. >> how do I turn off autovacuum on one database .. using

Re: [GENERAL] Configure Different Databases on One Server

On 10/29/2015 12:14 PM, Tom Lane wrote: Might be better to settle for configuring specific large tables using per-table vacuum settings, and not sweat the small stuff at a per-DB level. indeed, since you ALSO need to vacuum the system tables occasionally, or things go pear-shaped if you do

Re: [GENERAL] Configure Different Databases on One Server

Ok, Thanks! I think the ALTER DATABASE.. commands will work well for what I'm doing now. I want to experiment with one database (doing manual vacuuming, etc.) while leaving the other databases undisturbed. Regards, --Jim Longwill On 10/29/2015 12:36 PM, John R Pierce wrote: On 10/29/2015

[GENERAL] bdr-plugin packages repo not updated to 0.9.3?

http://packages.2ndquadrant.com/bdr/apt/pool/main/b/bdr-plugin/ Looks like the repo has not caught up to the latest 0.9.3 release. Would it be possible to push updated packages to the repo, please? It would be a great help with testing BDR. Thanks, -- Florin Andrei http://florin.myip.org/

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver wrote: > On 10/29/2015 12:10 PM, Dane Foster wrote: > >> On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce > > wrote: >> >> On 10/29/2015 11:20 AM, Dane Foster wrote: >>

Re: [GENERAL] mysql_fdw trouble

On 10/29/2015 11:20 AM, Dane Foster wrote: ​I think you are correct about mysql_fdw "... sending the trim() checks for remote execution" because according to the docs: "The latest version will push-down the foreign table where clause to the foreign server. The where condition on the foreign

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 12:29 PM, Eric Schwarzenbach wrote: Thank you! (Slapping head) Your regexp seems to do the trick. On 10/29/2015 01:49 PM, Rob Sargent wrote: On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint

[GENERAL] Configure Different Databases on One Server

I have a question about Postgres management. Our situation is probably similar to many data shops. We have one (Linux) server, and one Postgres installation (v9.4), hence one Postgres process-daemon set running on the server, but we have multiple databases created: ddev1, ddev2, ddev3. I

Re: [GENERAL] Configure Different Databases on One Server

On 10/29/2015 11:52 AM, Jim Longwill wrote: I have a question about Postgres management. Our situation is probably similar to many data shops. We have one (Linux) server, and one Postgres installation (v9.4), hence one Postgres process-daemon set running on the server, but we have multiple

Re: [GENERAL] mysql_fdw trouble

On 10/29/2015 12:10 PM, Dane Foster wrote: On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce > wrote: On 10/29/2015 11:20 AM, Dane Foster wrote: ​I think you are correct about mysql_fdw "... sending the trim() checks for remote

Re: [GENERAL] Domain check constraint not honored?

Rob Sargent wrote: > Also thought I should mention that there is an ip address type if that's > what you're trying to accomplish. Looking at the domain name, I wonder whether contrib/ltree would be helpful. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development,

Re: [GENERAL] Domain check constraint not honored?

Thank you! (Slapping head) Your regexp seems to do the trick. On 10/29/2015 01:49 PM, Rob Sargent wrote: On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint using a regexp to limit it to containing digits and

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce wrote: > On 10/29/2015 11:20 AM, Dane Foster wrote: > > ​I think you are correct about mysql_fdw "... sending the trim() checks > for remote execution" because according to the docs: > > "The latest version will push-down the

Re: [GENERAL] Configure Different Databases on One Server

Mr. Pierce, others, I spoke too soon on this. I'd like to do your alter database.. command but it isn't working. I've tried: postgres=# ALTER DATABASE ddev2 SET autovacuum = off; both as 'postgres' user, and the ddev2 owner user (which has owner privs), and I'm always getting this error:

Re: [GENERAL] Configure Different Databases on One Server

Adrian Klaver writes: > On 10/29/2015 01:35 PM, Jim Longwill wrote: >> ... I'm always getting this error: >> ERROR: parameter "autovacuum" cannot be changed now > Controls whether the server should run the autovacuum launcher > daemon. This is on by default;

Re: [GENERAL] mysql_fdw trouble

On 10/29/2015 12:56 PM, Dane Foster wrote: On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver > wrote: On 10/29/2015 12:10 PM, Dane Foster wrote: On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce

Re: [GENERAL] Configure Different Databases on One Server

On 10/29/2015 01:35 PM, Jim Longwill wrote: Mr. Pierce, others, I spoke too soon on this. I'd like to do your alter database.. command but it isn't working. I've tried: postgres=# ALTER DATABASE ddev2 SET autovacuum = off; both as 'postgres' user, and the ddev2 owner user (which has owner

Re: [GENERAL] pgxs/config/missing is... missing

On Oct 28, 2015, at 10:54 AM, Jim Nasby wrote: > I indeed get > > PERL = /bin/sh > /usr/local/lib/postgresql/pgxs/src/makefiles/../../config/missing perl Ew. > even after explicitly exporting PERL=/usr/local/bin/perl Hrm. I have this code in the pgTAP Makefile:

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 03:44 PM, Alvaro Herrera wrote: Rob Sargent wrote: Also thought I should mention that there is an ip address type if that's what you're trying to accomplish. Looking at the domain name, I wonder whether contrib/ltree would be helpful. Very observant! This is indeed part of a

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 04:29 PM, Eric Schwarzenbach wrote: On 10/29/2015 03:44 PM, Alvaro Herrera wrote: segment needing to be filled with zeros to a fixed length.) (Also FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$') Cheers, Eric So it can start with a dot, but not end

Re: [GENERAL] Domain check constraint not honored?

Eric Schwarzenbach writes: > ... (Also FWIW, the latest version of > this regexp is now '^([0-9]+.)*[0-9]+$') Um, that's not gonna do what you want at all. Outside brackets, a dot is a wildcard. (Regex syntax is a mess :-(.) regards, tom

Re: [GENERAL] Configure Different Databases on One Server

Ok. The reason I wanted to experiment with autovacuum disable for 'ddev2' database is that it is a mostly read-only database -- to support querying from our web apps. I.e. there are only 1 or 2 small tables ever updated all day on it, and only about 20 or so records total per day (compared

Re: [GENERAL] Better way to process boolean query result in shell-like situations?

On Thu, Oct 29, 2015 at 02:42:00AM +, Tim Landscheidt wrote: > Hi, > > I regularly run into the problem that I want to query a > PostgreSQL database in a script/program and depending on a > boolean result do one thing or the other. A typical example > would be a Puppet Exec that creates a

Re: [GENERAL] Configure Different Databases on One Server

On 10/29/2015 2:43 PM, Jim Longwill wrote: Ok. The reason I wanted to experiment with autovacuum disable for 'ddev2' database is that it is a mostly read-only database -- to support querying from our web apps. I.e. there are only 1 or 2 small tables ever updated all day on it, and only about

Re: [GENERAL] Configure Different Databases on One Server

Jim Longwill schrieb am 29.10.2015 um 22:43: Ok. The reason I wanted to experiment with autovacuum disable for 'ddev2' database is that it is a mostly read-only database -- to support querying from our web apps. I.e. there are only 1 or 2 small tables ever updated all day on it, and only about

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint using a regexp to limit it to containing digits and '.'. However I am finding I can add values with other characters to a column of this type. Is this to be

[GENERAL] BDR: name conflict when joining a rebuilt node

Let's say node pg12 in a cluster needs to be removed because it has serious problems. I remove it by running this command on another node in the cluster: SELECT bdr.bdr_part_by_node_names('{pg12}'); On pg12, I run this: BEGIN; SET LOCAL bdr.permit_unsafe_ddl_commands = true; SET LOCAL

Re: [GENERAL] mysql_fdw trouble

Hello, I think I've tripped over another mysq_fdw bug. I've filed a bug report on github already but just in case the problem is w/ my query I figured I would post it here in case someone sees something obvious. The error message I get is: null value in column "location" violates not-null

Re: [GENERAL] Domain check constraint not honored?

On Thu, Oct 29, 2015 at 7:16 PM, Rob Sargent wrote: > On 10/29/2015 05:01 PM, Tom Lane wrote: > > Eric Schwarzenbach > writes: > > ... (Also FWIW, the latest version of > this regexp is now '^([0-9]+.)*[0-9]+$') > >

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 8:22 PM, CaT wrote: > On Thu, Oct 29, 2015 at 08:38:49PM -0400, Dane Foster wrote: > > For the record I know top posting is a crime against god and humanity > but I > > feel justified because this post is not directly related to the original. > > So

[GENERAL] Hierarchical Query Question (PHP)

Can anyone tell me how to write the query described @ http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query ? The answer's very thorough, but I don't know how to string two queries and a function together like that. This doesn't work: $sql = "select * from

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 05:01 PM, Tom Lane wrote: Eric Schwarzenbach writes: ... (Also FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$') Um, that's not gonna do what you want at all. Outside brackets, a dot is a wildcard. (Regex syntax is a mess :-(.)

[GENERAL] ftell mismatch with expected position

I am getting the following warning when our program runs pg_dump.exe and the output is in custom format and send to standard out which is connected to a pipe (Windows platform). pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used The output of pg_dump is

[GENERAL] How can I change defined schema of linked tables when using Access linked table manager odbc connection

I am using postgresql 9.3 on Windows 8 64, and am using Access as a frontend data entry. In postgresql I have changed the schema from 'public' to 'irll'. The linked table in Access are still linked as 'public' and I need to update/change the connection of the tables to match the new schema in the

Re: [GENERAL] BDR: name conflict when joining a rebuilt node

On 30 October 2015 at 08:24, Florin Andrei wrote: > The problem is, bdr_node_join_wait_for_ready() never returns, it just waits > forever. If I go on pg11 and run SELECT * FROM bdr.bdr_nodes, I see pg12 > listed twice, with node_status k and i, respectively. On pg11 I see

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster wrote: > On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane wrote: > >> Dane Foster writes: >> > Installation and set up worked flawlessly but when I run the following >> query >> > ... >> > ​I

[GENERAL] Unable to select a table as postgres user

I have a database in which tables are created for each user when they create an account. These auto generated tables are used for generating analytics using a Function. This function is defined as a SECURITY DEFINER. So that any user who tries to execute this function will be doing so as a Super

Re: [GENERAL] pgxs/config/missing is... missing

On 10/29/15 5:31 PM, David E. Wheeler wrote: >even after explicitly exporting PERL=/usr/local/bin/perl Hrm. I have this code in the pgTAP Makefile: ifndef PERL PERL := $(shell which perl) endif Could that be causing the problem? That doesn't help because PGXS defines PERL: #