[GENERAL] postgresql93-9.3.5: deadlock when updating parent table expected?

2015-02-17 Thread Dmitry O Litvintsev
Hi, I recently updated to postgresql93-9.3.5 (from 9.2.9). I see frequent deadlocks when updating parent table in insert into child table. There is foreign key constraint between child table and parent table. Parent table is updated on by trigger in insert into child table. So pretty much st

Re: [GENERAL] which is better- storing data as array or json?

2015-02-17 Thread David G. Johnston
On Tue, Feb 17, 2015 at 9:00 PM, zach cruise wrote: > i can't keep creating tables ​Where did you get this idea?​ or adding columns ​Of course not...​ every time i need to > add a nickname- this happens a lot. > > ​OK​... > so i want to put everything in an array or json. > > ​Those are n

Re: [GENERAL] which is better- storing data as array or json?

2015-02-17 Thread John R Pierce
On 2/17/2015 8:00 PM, zach cruise wrote: i can't keep creating tables or adding columns every time i need to add a nickname- this happens a lot. so i want to put everything in an array or json. remember rows can have different number of nicknames. david was suggesting a join table. one table

Re: [GENERAL] which is better- storing data as array or json?

2015-02-17 Thread zach cruise
i can't keep creating tables or adding columns every time i need to add a nickname- this happens a lot. so i want to put everything in an array or json. remember rows can have different number of nicknames. On 2/17/15, David G Johnston wrote: > zach cruise wrote >> for indexing, accessing, fil

Re: [GENERAL] Revoking access for pg_catalog schema objects

2015-02-17 Thread David G Johnston
Tom Lane-2 wrote > Saimon < > aimon.slim@ > > writes: >> I want to restrict access for some user for tables and views in >> pg_catalog >> schema. > > The system is not designed to support this, and you should not expect to > succeed at hiding things this way. I would expect a note at: http://w

Re: [GENERAL] window function ordering not working as expected

2015-02-17 Thread Lonni J Friedman
On Tue, Feb 17, 2015 at 4:18 PM, Tom Lane wrote: > Lonni J Friedman writes: >> I'm interested in seeing: >> * the date for the most recent result >> * test name (identifier) >> * most recent result (decimal value) >> * the worst (lowest decimal value) test result from the past 21 days >> * the da

Re: [GENERAL] which is better- storing data as array or json?

2015-02-17 Thread David G Johnston
zach cruise wrote > for indexing, accessing, filtering and searching? > > as simple array- > first name| last name | nicknames > tom | jerry | {cat}, {mouse} > > as multi-dimensional array- > first name| last name | nicknames > tom | jerry | {cat, kat}, {mouse, mice} > > as simple json-

[GENERAL] which is better- storing data as array or json?

2015-02-17 Thread zach cruise
for indexing, accessing, filtering and searching? as simple array- first name | last name | nicknames tom | jerry | {cat}, {mouse} as multi-dimensional array- first name | last name | nicknames tom | jerry | {cat, kat}, {mouse, mice} as simple json- first name | last name | nickna

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> This is the standard mistake about pg_dump, which is to imagine that it >> depends only on userspace operations while inspecting schema info. It >> doesn't; it makes use of things like ruleutils.c which operate on "latest >> availa

Re: [GENERAL] window function ordering not working as expected

2015-02-17 Thread Tom Lane
Lonni J Friedman writes: > I'm interested in seeing: > * the date for the most recent result > * test name (identifier) > * most recent result (decimal value) > * the worst (lowest decimal value) test result from the past 21 days > * the date which corresponds with the worst test result from the p

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > The issue is that pg_dump wants to lock the table against changes, which > > is really to prevent the table to change between "we got the definition > > of the table" and "pulling the records out of the table." It's not > > immedi

[GENERAL] window function ordering not working as expected

2015-02-17 Thread Lonni J Friedman
Greetings, I have a postgresql-9.3.x database with a table with a variety of date stamped test results, some of which are stored in json format (natively in the database). I'm attempting to use some window functions to pull out specific data from the test results over a a time window, but part of t

Re: [GENERAL] Revoking access for pg_catalog schema objects

2015-02-17 Thread Tom Lane
Saimon writes: > I want to restrict access for some user for tables and views in pg_catalog > schema. The system is not designed to support this, and you should not expect to succeed at hiding things this way. regards, tom lane -- Sent via pgsql-general mailing list (p

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Tom Lane
Stephen Frost writes: > * Daniel LaMotte (lamott...@gmail.com) wrote: >> I understand this. This is the behavior I want. What I don't understand >> is why the readonly user can inspect the schema of the table interactively >> when pg_dump refuses to do the same via the command line (assumably it

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Stephen Frost
Melvin, * Melvin Davidson (melvin6...@gmail.com) wrote: > Simply put, giving access to a schema DOES NOT automatically give access to > any table in the schema. So if you want a specific user ( or role) to be > able to read (or pg_dump) all tables in the schema, then you must GRANT > SELECT of all

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Melvin Davidson
Simply put, giving access to a schema DOES NOT automatically give access to any table in the schema. So if you want a specific user ( or role) to be able to read (or pg_dump) all tables in the schema, then you must GRANT SELECT of all tables in that schema to the user (or role). On Tue, Feb 17, 20

Re: [GENERAL] Determine all listeners subscribed to notifcations and what channels

2015-02-17 Thread Merlin Moncure
On Tue, Feb 17, 2015 at 4:01 PM, Tom Lane wrote: > Cory Tucker writes: >> I'm interested in trying to figure out which channels have been subscribed >> to (using LISTEN). From what I could tell via a little Googling, there >> used to be a table named pg_catalog.pg_listener that contained all thi

[GENERAL] Revoking access for pg_catalog schema objects

2015-02-17 Thread Saimon
Hi I want to restrict access for some user for tables and views in pg_catalog schema. After the following command in psql: REVOKE ALL ON SCHEMA pg_catalog FROM PUBLIC; Access, for example, for table pg_proc was restricted: SELECT * from pg_catalog.pg_proc; > ERROR: permission denied for schema p

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Stephen Frost
Daniel, * Daniel LaMotte (lamott...@gmail.com) wrote: > I understand this. This is the behavior I want. What I don't understand > is why the readonly user can inspect the schema of the table interactively > when pg_dump refuses to do the same via the command line (assumably it asks > for too muc

[GENERAL] BDR Monitoring, missing pg_stat_logical_decoding view

2015-02-17 Thread Steve Boyle
I'm trying to setup replication monitoring for BDR, following the doc here: https://wiki.postgresql.org/wiki/BDR_Monitoring My BDR installs seem to be missing the pg_stat_logical_decoding view. Is there something specific I need to do to install/create that view? Thanks, Steve Boyle -- Sent

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Daniel LaMotte
I understand this. This is the behavior I want. What I don't understand is why the readonly user can inspect the schema of the table interactively when pg_dump refuses to do the same via the command line (assumably it asks for too much permission when simply trying to dump the schema [NOT the tab

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Daniel LaMotte
The point is that the user seems to have permissions to view the schema but not the table data. If I can interactively inspect the table schema but pg_dump is unable to dump the table schema, that seems like a bug. The account explicitly is not allowed access to the table's data but seems to be a

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Adrian Klaver
On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U postgres -P) but I got the message: pg_basebackup: directory "E:\Data\Da

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Adrian Klaver
On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U postgres -P) but I got the message: pg_basebackup: directory "E:\Data\Da

Re: [GENERAL] Determine all listeners subscribed to notifcations and what channels

2015-02-17 Thread Tom Lane
Cory Tucker writes: > I'm interested in trying to figure out which channels have been subscribed > to (using LISTEN). From what I could tell via a little Googling, there > used to be a table named pg_catalog.pg_listener that contained all this > information, but that seems to have disappeared som

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Igor Neyman
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Tuesday, February 17, 2015 4:12 PM To: Daniel LaMotte Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Issue dumping schema using readonly user

Re: [GENERAL] Determine all listeners subscribed to notifcations and what channels

2015-02-17 Thread Igor Neyman
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Cory Tucker Sent: Tuesday, February 17, 2015 4:21 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Determine all listeners subscribed to notifcations and what channels I'm interested in tryi

[GENERAL] Determine all listeners subscribed to notifcations and what channels

2015-02-17 Thread Cory Tucker
I'm interested in trying to figure out which channels have been subscribed to (using LISTEN). From what I could tell via a little Googling, there used to be a table named pg_catalog.pg_listener that contained all this information, but that seems to have disappeared somewhere in the 9.x release (I'

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Adrian Klaver
On 02/17/2015 08:43 AM, Daniel LaMotte wrote: I understand this. This is the behavior I want. What I don't understand is why the readonly user can inspect the schema of the table interactively when pg_dump refuses to do the same via the command line (assumably it asks for too much permission wh

Re: [GENERAL] Missing table from in INSERT RETURNING

2015-02-17 Thread John McKown
On Tue, Feb 17, 2015 at 2:15 PM, David G Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Feb 17, 2015 at 1:08 PM, John McKown [via PostgreSQL] <[hidden > email] > wrote: > >> I haven't seen any one else reply. I don't know if you've go

Re: [GENERAL] Missing table from in INSERT RETURNING

2015-02-17 Thread David G Johnston
On Tue, Feb 17, 2015 at 1:08 PM, John McKown [via PostgreSQL] < ml-node+s1045698n5838306...@n5.nabble.com> wrote: > I haven't seen any one else reply. I don't know if you've gotten a > solution. But the following seemed to work for me: > > ​mine apparently got bounced...​ > WITH serie AS ( > se

Re: [GENERAL] Missing table from in INSERT RETURNING

2015-02-17 Thread John McKown
On Tue, Feb 17, 2015 at 2:07 PM, John McKown wrote: > I haven't seen any one else reply. I don't know if you've gotten a > solution. But the following seemed to work for me: > > WITH serie AS ( > select s, s*10 as computing > from generate_series(1,10) as s > ) > INSERT INTO test_insert_returning

Re: [GENERAL] Missing table from in INSERT RETURNING

2015-02-17 Thread John McKown
I haven't seen any one else reply. I don't know if you've gotten a solution. But the following seemed to work for me: WITH serie AS ( select s, s*10 as computing from generate_series(1,10) as s ) INSERT INTO test_insert_returning (some_value) SELECT computing FROM serie RETURNING gid, some_value;

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Guillaume Drolet
This provides part of the answer to my previous post, from the 9.4 doc (although I'm running 9.3 but I guess the second phrase in the paragraph applies to my case): Tablespaces will in plain format by default be backed up to the same path they have on the server, unless the option --tablespace-map

[GENERAL] Missing table from in INSERT RETURNING

2015-02-17 Thread Rémi Cura
Hello dear list, I would appreciate some help on a small matter that has been bothering me for a long time : CREATE TABLE test_insert_returning( gid SERIAL ,some_value int ); WITH serie AS ( select s, s*10 as computing from generate_series(1,10) as s ) INSERT INTO test_insert_returning (some_valu

Re: [GENERAL] Issue dumping schema using readonly user

2015-02-17 Thread Adrian Klaver
On 02/17/2015 03:11 AM, Daniel LaMotte wrote: > The point is that the user seems to have permissions to view the schema > but not the table data. If I can interactively inspect the table schema > but pg_dump is unable to dump the table schema, that seems like a bug. > > The account explicitly i

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Guillaume Drolet
Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U postgres -P) but I got the message: pg_basebackup: directory "E:\Data\Database" exists but is not empty" I creatde a tabl