[GENERAL] From the "SQL is verbose" department, WINDOW RANGE specifications

The default range specification is: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW It seems like a common second choice is to want: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Why did they have to make something so common take 49 characters that, for seldom-using users, is

Re: [GENERAL] Make "(composite).function_name" syntax work without search_path changes?

"David G. Johnston" writes: > The system knows that the datatype being inspected is "altschema.alttype" - > would it be reasonable for the system to check for a function named "label" > in the same schema as the target type, "altschema", with the target > argument type

Re: [GENERAL] query not scaling

On Thu, 2017-10-26 at 19:07 -0600, Rob Sargent wrote: >    ->  Nested Loop  (cost=3799.40..44686205.23 rows=1361304413 width=40) > (actual time=55.443..89684.451 rows=75577302 loops=1) >  ->  Hash Join  (cost=3798.98..43611.56 rows=823591 width=32) >

[GENERAL] pgaduit - is there a way to audit a role

Is there a way to audit a group like as follows alter role db_rw set pgaudit.log = 'read,write,function,ddl' and then any user part of db_rw role can be audited automatically. It does not seem to work if I connect to the db as rakesh who is part of db_rw role. -- Sent from:

[GENERAL] Make "(composite).function_name" syntax work without search_path changes?

CREATE SCHEMA altschema; CREATE TYPE altschema.alttype AS ( altid text, altlabel text ); CREATE FUNCTION altschema.label(item altschema.alttype) RETURNS text LANGUAGE sql AS $$ SELECT (item).altlabel; $$; WITH vals (v) AS ( SELECT ('1', 'One')::altschema.alttype ) SELECT (v).label FROM vals; --

Re: [GENERAL] pg_audit to mask literal sql

By mask I mean pgaudit should log where ssn = '123-456-7891' as where ssn = '?' -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] pg_audit to mask literal sql

Yes all who interact with HIPAA data are trained for HIPAA SOP. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] pg_audit to mask literal sql

No they do select. It is fine in HIPAA to view data which are protected, if it is part of your job. What is not fine is being careless with that protected data and let unauthorized person view that data. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html --

Re: [GENERAL] pg_audit to mask literal sql

On 10/30/2017 03:35 PM, John R Pierce wrote: On 10/30/2017 10:55 AM, rakeshkumar464 wrote: Is there a way in pgaudit to mask literal sqls like the below: insert into table (col1,col2) values(1,2) select * from table where col1 = 1 These sqls are typed by our QA folks using pgadmin. pgaudit

Re: [GENERAL] pg_audit to mask literal sql

On 10/30/2017 10:55 AM, rakeshkumar464 wrote: Is there a way in pgaudit to mask literal sqls like the below: insert into table (col1,col2) values(1,2) select * from table where col1 = 1 These sqls are typed by our QA folks using pgadmin. pgaudit records this verbatim which runs afoul of our

Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?

On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras wrote: > > 3. But they do log in with "developer" roles which are inherited from the > owner role. > > ​[...]​ > I've tried it on a dummy database and it apparently works as described > here. Is this by design? > > ​Not quite

Re: [GENERAL] pg_audit to mask literal sql

On Mon, Oct 30, 2017 at 10:55:17AM -0700, rakeshkumar464 wrote: > Is there a way in pgaudit to mask literal sqls like the below: > > insert into table (col1,col2) values(1,2) > select * from table where col1 = 1 > > These sqls are typed by our QA folks using pgadmin. pgaudit records this >

[GENERAL] Roles inherited from a role which is the owner of a database can drop it?

Hello, I just want to verify that what I'm observing is true, and if it is, I'd like to know how to avoid it: 1. There are databases owned by a certain role which is a superuser 2. Nobody logs in with the superuser role unless necessary 3. But they do log in with "developer" roles which are

[GENERAL] pg_audit to mask literal sql

Is there a way in pgaudit to mask literal sqls like the below: insert into table (col1,col2) values(1,2) select * from table where col1 = 1 These sqls are typed by our QA folks using pgadmin. pgaudit records this verbatim which runs afoul of our HIPAA requirement. Prepared statements are not

[GENERAL] the database system is shutting down - terminating walsender process due to replication timeout

Greetings everyone, I'm looking for a bit of help understanding a particular behavior we are seeing with our PostgreSQL 9.6. After issuing a service shutdown command with "systemctl stop" on RHEL 7 our PostgreSQL instance started behaving weirdly. For the first time it wouldn't shutdown so

Re: [GENERAL] gin index trouble

On 10/30/2017 10:56 AM, Peter Geoghegan wrote: On Mon, Oct 30, 2017 at 9:45 AM, Rob Sargent wrote: Peter, you beat me to the punch. I was just about to say "Having read the referenced message I thought I would add that we never delete from this table." In this

Re: [GENERAL] gin index trouble

On Mon, Oct 30, 2017 at 9:45 AM, Rob Sargent wrote: > Peter, you beat me to the punch. I was just about to say "Having read the > referenced message I thought I would add that we never delete from this > table." In this particular case it was written to record by record,

Re: [GENERAL] gin index trouble

On 10/30/2017 10:32 AM, Peter Geoghegan wrote: On Fri, Oct 27, 2017 at 3:15 PM, Rob Sargent wrote: I’ve hit this same message Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN page is of different type in a couple of contexts and I’m starting

Re: [GENERAL] UPDATE syntax change

Adam Brusselback writes: > --works > UPDATE tst_table > SET (b, c) = ('help me', 'please') > WHERE a = 0; > --does not work > UPDATE tst_table > SET (b) = ('help me') > WHERE a = 0; > So there was a change made, and you now cannot use the multi-column > syntax if

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

Appreciate the link, didn't come up when I was googling the issue. As you said, a mention in the release notes would have been helpful. Thanks, -Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Backup strategy using 'wal_keep_segments'

Greetings, * Rhhh Lin (ruanline...@hotmail.com) wrote: > A colleague recently suggested that instead of implementing an > 'archive_command' to push archivable WALs to a secondary location (for > further backup to tape for example), we could instead persist the WAL files > in their current

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

On Mon, Oct 30, 2017 at 12:29:03PM -0400, Adam Brusselback wrote: > I have some queries that were working in 9.6 which suddenly broke when > moving to 10. > > Digging in, the error i'm getting is: ERROR: source for a > multiple-column UPDATE item must be a sub-SELECT or ROW() expression > So

Re: [GENERAL] gin index trouble

On Fri, Oct 27, 2017 at 3:15 PM, Rob Sargent wrote: > I’ve hit this same message > > Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN > page is of different type > > in a couple of contexts and I’m starting to get worried. > > I’ve rebuilt the

[GENERAL] UPDATE syntax change

Hey all, just getting around to updating my development environment to Postgres 10, and there was something I found while in testing. Version info: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit I have some queries that were working in 9.6 which

Re: [GENERAL] gin index trouble

Rob Sargent writes: >> If you can make a test case that (eventually) hits that, we'd be >> interested to see it ... > Any hint(s) on what might trigger this sort of thing? I could duplicate > the upload, but I doubt you want the 800K records, 200M input file even > if

Re: [GENERAL] gin index trouble

On Mon, Oct 30, 2017 at 7:35 AM, Tom Lane wrote: > Rob Sargent writes: >> I’ve hit this same message >> Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN >> page is of different type >> in a couple of contexts and I’m starting

Re: [GENERAL] gin index trouble

If you can make a test case that (eventually) hits that, we'd be interested to see it ... Any hint(s) on what might trigger this sort of thing? I could duplicate the upload, but I doubt you want the 800K records, 200M input file even if it did regenerate the problem. Would select * from

Re: [GENERAL] gin index trouble

Rob Sargent writes: > I’ve hit this same message > Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN > page is of different type > in a couple of contexts and I’m starting to get worried. If you can make a test case that (eventually) hits that,

Re: [GENERAL] starting PG command line options vs postgresql.con

On Mon, Oct 30, 2017 at 2:08 PM, David G. Johnston wrote: > On Mon, Oct 30, 2017 at 6:48 AM, rakeshkumar464 > wrote: >> >> I would prefer using postgresql.conf. what is the consensus in this forum >> regarding command line vs

Re: [GENERAL] Comparing epoch to timestamp

# EXPLAIN SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE added > to_timestamp(0) UNION SELECT ARRAY_AGG(hashed) FROM words_verbs WHERE added > to_timestamp(0)

Re: [GENERAL] starting PG command line options vs postgresql.con

On Mon, Oct 30, 2017 at 6:48 AM, rakeshkumar464 wrote: > I would prefer using postgresql.conf. what is the consensus in this forum > regarding command line vs postgresql.conf. ​I suspect that most people administering a PostgreSQL database would expect that the

Re: [GENERAL] starting PG command line options vs postgresql.con

rakeshkumar464 writes: > I am new to Docker env and I see that PG, as a container is started with > [ lots of command-line parameters ] > I would prefer using postgresql.conf. what is the consensus in this forum > regarding command line vs postgresql.conf. Also if

[GENERAL] starting PG command line options vs postgresql.con

I am new to Docker env and I see that PG, as a container is started with parameters like this: docker run -it \ --detach \ --name name \ --restart=unless-stopped \ -p 5432:5432 \ -e PGDATA=/var/lib/postgresql/data/pg10 -N 500 \ -B 3GB \ -S 6291kB \ -c listen_addresses=* \ -c

Re: [GENERAL] Fwd: SPI_palloc problem

On Sun, Oct 29, 2017, at 07:40 PM, Aron Widforss wrote: > I mailed this from my main email address instead of the one I'm > subscribed to pgsql-general with. So, here goes. > > - Original message - > From: Aron Widforss > To: pgsql-general@postgresql.org > Subject:

Re: [GENERAL] Comparing epoch to timestamp

Sorry, I probably had to call: # EXPLAIN SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE EXTRACT(EPOCH FROM added) > 0 UNION SELECT ARRAY_AGG(hashed) FROM words_verbs WHERE EXTRACT(EPOCH FROM

[GENERAL] gin index trouble

I’ve hit this same message Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN page is of different type in a couple of contexts and I’m starting to get worried. I’ve rebuilt the index, but will that help? Is there a way to see what the ‘different type’ is? Is it

[GENERAL] Comparing epoch to timestamp

Hello, in PostgreSQL 9.5 I have a table with 67000 records: # \d words_nouns Table "public.words_nouns" Column | Type | Modifiers -+--+--- word| text | not null hashed | text