Re: [GENERAL] Select works only when connected from login postgres

> So is the 9.4 instance the production/live database? Essentially, but it's not heavily used: this is me messing around on a dev box. > So what happens when you specify the port in your psql connection, eg: > /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432 >

Re: [GENERAL] Select works only when connected from login postgres

On 12/03/2016 07:38 PM, Joseph Brenner wrote: Our story thus far: I've now got three different pg installations, with three servers running simultaneously: ps ax | egrep postgres | egrep '\-D' 748 ?S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c

Re: [GENERAL] Select works only when connected from login postgres

>For kicks, how about \echo or \! Something that doesn't need a server to work. Sure: those do work. doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom psql (9.6.1) Type "help" for help. doom=# select 'hello' as world; doom=# \echo 'yo' yo doom=# \! ls -lad p* drwxr-xr-x 1

Re: [GENERAL] Select works only when connected from login postgres

On Saturday, December 3, 2016, Joseph Brenner wrote: > > doom=# select 'hello' as world; > doom=# > > Nothing else gives me any output either: \l, \du, etc. > > For kicks, how about \echo or \! Something that doesn't need a server to work. David J,

Re: [GENERAL] Select works only when connected from login postgres

Our story thus far: I've now got three different pg installations, with three servers running simultaneously: ps ax | egrep postgres | egrep '\-D' 748 ?S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.co

Re: [GENERAL] Where to download pgbench for Windows 7

Thank you, John. On Dec 2, 2016 23:12, "John R Pierce" wrote: > On 12/2/2016 10:37 PM, James Zhou wrote: > >> I am new to PostgreSQL and am leaning it. I installed PostgreSQL on a >> Windows 7 laptop and would like to play with pgbench to generate a sample >> database and a

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On Fri, 2016-12-02 at 13:45 -0800, Adrian Klaver wrote: > > On 12/02/2016 09:40 AM, Tom DalPozzo wrote: > > > > > > Hi, > > I've two tables, t1 and t2, both with one bigint id indexed field > > and > > one 256 char data field; t1 has always got 1 row, while t2 is > > increasing as explained

Re: [GENERAL] Strange activity of prepared queries

On Fri, 2016-12-02 at 07:04 -0700, pinker wrote: > > Hi! > I have pgBadger report with strange data about prepared queries I > cannot > interpret by myself. If anybody could help me with interpretation, > would be > greatly appreciated. > In first half of the day pgBadger shows there is no

Re: [GENERAL] Index size

On Sat, Dec 3, 2016 at 9:32 PM, Steve Atkins wrote: > > > On Dec 3, 2016, at 3:57 PM, Samuel Williams com> wrote: > > > > Thanks everyone for your feedback so far. I've done a bit more digging: > > > > MySQL in MBytes (about 350 million rows): >

Re: [GENERAL] No select privileges when not connecting from login postgres

> Save yourself some trouble and use the PGDG repos... Yes, I was just trying one of those a little while ago. It exhibits the exact same behavior as my build from scratch. > In your psql commands you do not show connecting to port 5433 That's correct: my reading of the installation

Re: [GENERAL] Index size

> On Dec 3, 2016, at 3:57 PM, Samuel Williams > wrote: > > Thanks everyone for your feedback so far. I've done a bit more digging: > > MySQL in MBytes (about 350 million rows): > > index_user_event_on_what_category_id_created_at_latlng | 22806.00 >

Re: [GENERAL] Avoid sorting when doing an array_agg

On Sat, 2016-12-03 at 13:08 -0500, Kiriakos Georgiou wrote: > The array_agg() has nothing to do with it.  It’s the group by. > Without knowing what you are conceptually trying to accomplish, I > can’t say much. It *IS* caused by array_agg(). PostgreSQL can only do HashAggregate when everything

Re: [GENERAL] Postrgres-XL and Postgres-BDR

Contact any of the professional support organizations and ask for whom they work. Some will, some won't name names. Sent from my iPad > On Dec 3, 2016, at 5:41 PM, Michael Paquier wrote: > >> On Tue, Nov 29, 2016 at 10:25:50AM -0200, Saulo Tadeu wrote: >> I'm would

Re: [GENERAL] Select works only when connected from login postgres

Joseph Brenner writes: > I'm trying to get a new build of 9.6.1 working on Debian > stable and I'm seeing some odd behavior where things work > correctly if I run psql when logged in as user 'postgres', > but if I'm logged-in as user 'doom' (my usual login), I don't > seem to

Re: [GENERAL] No select privileges when not connecting from login postgres

On 03/12/16 22:55, Joseph Brenner wrote: The version in the Debian stable repos right now is 9.4, and I saw an issue with it I wanted to check against the latest version, so I did a build of it from a tarball. You can get the latest version from the PostgreSQL apt repo:

Re: [GENERAL] No select privileges when not connecting from login postgres

On 12/03/2016 02:55 PM, Joseph Brenner wrote: The version in the Debian stable repos right now is 9.4, and I saw an issue with it I wanted to check against the latest version, so I did a build of it from a tarball. Save yourself some trouble and use the PGDG repos:

Re: [GENERAL] Postrgres-XL and Postgres-BDR

On Tue, Nov 29, 2016 at 10:25:50AM -0200, Saulo Tadeu wrote: > I'm would like to know which companies use postgres-XL and Postgres-BDR. > Could you name some companies? Usually companies are not willing to disclose information regarding the infrastructure they are using. -- Michael

Re: [GENERAL] Index size

Thanks everyone for your feedback so far. I've done a bit more digging: MySQL in MBytes (about 350 million rows): index_user_event_on_what_category_id_created_at_latlng | 22806.00 index_user_event_for_reporting | 18211.00 index_user_event_on_created_at | 9519.00 index_user_event_on_user_id |

Re: [GENERAL] Select works only when connected from login postgres

Yes, and sorry about the re-post. I thought my original message was hung-up in moderation, so I was doing an unsub/resub fandango to get email addresses to match. On Sat, Dec 3, 2016 at 12:13 PM, Adrian Klaver wrote: > On 12/03/2016 12:08 PM, Joseph Brenner wrote: >>

Re: [GENERAL] No select privileges when not connecting from login postgres

> Any unusual errors in the logs? Or maybe a "\o /somefile" in your ~doom/.psqlrc? No, nothing much in the logs after "autovacuum launcher started", and I don't have a .psqlrc file. On Sat, Dec 3, 2016 at 6:56 AM, Julien Rouhaud wrote: > On Wed, Nov 30, 2016 at

Re: [GENERAL] No select privileges when not connecting from login postgres

The version in the Debian stable repos right now is 9.4, and I saw an issue with it I wanted to check against the latest version, so I did a build of it from a tarball. (Admittedly, there's no particular reason I need to be running stable, and I was just wondering the other day why I wasn't using

Re: [GENERAL] No select privileges when not connecting from login postgres

Logged in as "doom", -V shows version 9.6.1 as I'd expect: /usr/local/pgsql/bin/psql -V psql (PostgreSQL) 9.6.1 To *literally* answer your question though, I have a different version installed in my $PATH: psql -V psql (PostgreSQL) 9.4.9 But yes, I've been invoking psql the same way as login

Re: [GENERAL] Moving pg_xlog

Thanks, everyone, for your comments. I think I've got a clearer idea of what's going on now... Robert. On 1 December 2016 at 13:55, Robert Inder wrote: > I'm running Postgres9.4 in master/hot-standby mode on a few pairs of servers. > > While recovering from A Bit Of

Re: [GENERAL] Select works only when connected from login postgres

On 12/03/2016 12:08 PM, Joseph Brenner wrote: I'm trying to get a new build of 9.6.1 working on Debian stable and I'm seeing some odd behavior where things work correctly if I run psql when logged in as user 'postgres', but if I'm logged-in as user 'doom' (my usual login), I don't seem to have

[GENERAL] Select works only when connected from login postgres

I'm trying to get a new build of 9.6.1 working on Debian stable and I'm seeing some odd behavior where things work correctly if I run psql when logged in as user 'postgres', but if I'm logged-in as user 'doom' (my usual login), I don't seem to have any select privileges. Even this fails silently:

Re: [GENERAL] Avoid sorting when doing an array_agg

The array_agg() has nothing to do with it.  It’s the group by. Without knowing what you are conceptually trying to accomplish, I can’t say much. On my test 9.4.10 db, a similar example does a HashAggregate, so no sorting (google HashAggregate vs GroupAggregate).  But still it’s an expensive

Re: [GENERAL] No select privileges when not connecting from login postgres

On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote: > I'm trying to get a new build of 9.6.1 working on a machine > running Debian stable (jessie) and I'm seeing some odd > behavior where things work correctly if I run psql when > logged in as postgres, but if I run it as user 'doom' (my >

Re: [GENERAL] No select privileges when not connecting from login postgres

On 11/30/2016 08:48 PM, Joseph Brenner wrote: I'm trying to get a new build of 9.6.1 working on a machine running Debian stable (jessie) and I'm seeing some odd behavior where things work correctly if I run psql when logged in as postgres, but if I run it as user 'doom' (my usual login), I don't

Re: [GENERAL] Any work being done on materialized view?

What exactly are the features you are looking for? Materialized views are treated as tables in PostgreSQL and you can create indexes on them. https://www.postgresql.org/docs/9.4/static/sql-creatematerializedview.html "CREATE MATERIALIZED VIEW is *similar to **CREATE TABLE* AS, except that it

Re: [GENERAL] No select privileges when not connecting from login postgres

On Wed, Nov 30, 2016 at 08:48:41PM -0800, Joseph Brenner wrote: > I'm trying to get a new build of 9.6.1 working on a machine > running Debian stable (jessie) and I'm seeing some odd > behavior where things work correctly if I run psql when > logged in as postgres, but if I run it as user 'doom'

[GENERAL] Any work being done on materialized view?

Is there any work being done on materialized views for version 9.7? This postgresql feature is severely lacking compared to similar features like indexed views by sql server.

[GENERAL] Avoid sorting when doing an array_agg

I have a users table which contains ~70 million rows that looks like this: Column| Type| -+---+ id | integer | first_name | character varying | last_name | character varying | category_id | integer | Indexes:

[GENERAL] No select privileges when not connecting from login postgres

I'm trying to get a new build of 9.6.1 working on a machine running Debian stable (jessie) and I'm seeing some odd behavior where things work correctly if I run psql when logged in as postgres, but if I run it as user 'doom' (my usual login), I don't seem to have any select privileges. Even this

[GENERAL] Postrgres-XL and Postgres-BDR

Hi I'm would like to know which companies use postgres-XL and Postgres-BDR. Could you name some companies? thank's, Saulo Tadeu