Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

2017-06-05 Thread Jerry Sievers
Bruce Momjian <br...@momjian.us> writes:

> On Wed, May 31, 2017 at 08:28:51AM -0700, Mark Dilger wrote:
>
>> > Uh, I thought only the sessions that created the temporary objects could
>> > see them, and since they are not in WAL and autovacuum can't see them,
>> > their non-existence in a temporary tablespace would not be a problem.
>> 
>> You are correct.  I was thinking about an extension to allow unlogged
>> tablespaces on temporary filesystems, but got the words "unlogged" and
>> "temporary" mixed up in my thinking and in what I wrote.  I should have
>> written that unlogged tablespaces would only host unlogged tables and
>> unlogged indexes, such that users are not surprised to find their data
>> missing.

Very late to the discussion here...

At my site, we have an NVME disk as temp_tablespace.

It's used not only for disk-spilling temp operations.  There are temp
tables, UNLOGGEd persistent tables.

And you guessed it.  In spite of our warnings, users have created LOGGEd
tables in there.

This has made on a few ocasions our SAN snapshots, started on a
different system fail apparently due to something in the crash recovery
data trying to update pages on one of the real tables :-)

The SAN snaps capture the entire pgdata and WAL pg_xlog area but there
is no attempt to copy the NVME device when the snaps are made.

There's an event trigger plus batch job now running tou avoid this risk.

We realize too that there are implications here if a backup is
instantiated and PITR is done.

Just FYI that there could be others running like this ignorant of the
potential gotchas.

>> 
>> On reflection, I think both features are worthwhile, and not at all exclusive
>> of each other, though unlogged tablespaces is probably considerably more
>> work to implement.
>
> TODO item added:
>
>   Allow tablespaces on RAM-based partitions for temporary objects 
>
> and I wrote a blog entry about this:
>
>   https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017
>
> -- 
>   Bruce Momjian  <br...@momjian.us>http://momjian.us
>   EnterpriseDB         http://enterprisedb.com
>
> + As you are, so once was I.  As I am, so you will be. +
> +  Ancient Roman grave inscription +

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Feature request: make cluster_name GUC useful for psql prompts

2016-05-06 Thread Jerry Sievers
Steve Crawford <scrawf...@pinpointresearch.com> writes:

> That is almost identical to the solution I suggested a week or two ago to 
> someone tackling the issue and the hack works on initial connection.
>
> Connect to a different cluster with "\c", however, and it will leave the 
> prompt showing you connected to the original database which is not good.

True and I've always thought of it as a possible misfeature of psql that
it scans .psqlrc only once.

> Cheers,
> Steve
>
> On Fri, May 6, 2016 at 11:42 AM, Jerry Sievers <gsiever...@comcast.net> wrote:
>
> Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:
>
> > On 5/5/16 9:21 PM, Steve Crawford wrote:
> >
> >> Adding an escape sequence that references cluster_name would enable
> >> prompts to identify the cluster in a manner that is both consistent and
> >> distinct regardless of access path.
> >
> > I think that would be a good idea.  You could probably design it so
> > that any server parameter reported to the client can be put in a psql
> > prompt.
>
> The OP can easily work around that lack of support with something such as 
> follow...
>
> Add this to ~/.psqlrc[-optional version stuff]
>
> select setting as cluster_name from pg_settings where name = 
> 'cluster_name'  -- do not simicolon terminate this line
> \gset
>
> \set PROMPT1 :cluster_name ': how cool is this:'
>
> >
> >> Potential issues/improvements:
> >>
> >> What should the escape-sequence display if cluster_name is not set or
> >> the cluster is a pre-9.5 version. %M? %m?
> >>
> >> In future server versions should there be a default for cluster_name if
> >> it is not set? If so, what should it be? Would the server canonical
> >> hostname + listen-port be reasonable?
> >
> > Those are good questions.  I don't really like the proposed answers,
> > because that could cause confusion in practical use.
> >
> > --
> > Peter Eisentraut              http://www.2ndQuadrant.com/
> > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>

-- 
Jerry Sievers
e: jerry.siev...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Feature request: make cluster_name GUC useful for psql prompts

2016-05-06 Thread Jerry Sievers
Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:

> On 5/5/16 9:21 PM, Steve Crawford wrote:
>
>> Adding an escape sequence that references cluster_name would enable
>> prompts to identify the cluster in a manner that is both consistent and
>> distinct regardless of access path.
>
> I think that would be a good idea.  You could probably design it so
> that any server parameter reported to the client can be put in a psql
> prompt.

The OP can easily work around that lack of support with something such as 
follow...

Add this to ~/.psqlrc[-optional version stuff]

select setting as cluster_name from pg_settings where name = 'cluster_name'  -- 
do not simicolon terminate this line
\gset

\set PROMPT1 :cluster_name ': how cool is this:'

>
>> Potential issues/improvements:
>>
>> What should the escape-sequence display if cluster_name is not set or
>> the cluster is a pre-9.5 version. %M? %m?
>>
>> In future server versions should there be a default for cluster_name if
>> it is not set? If so, what should it be? Would the server canonical
>> hostname + listen-port be reasonable?
>
> Those are good questions.  I don't really like the proposed answers,
> because that could cause confusion in practical use.
>
> -- 
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Pg-Logical output pkg; can't install 9.4 and 9.5 on same Wheezy box

2016-03-29 Thread Jerry Sievers
Hackers, please see below.

Posted on Admin and then General but not sure  anyone of authority has noticed.

Briefly, conflicting  files in an include directory prevent 9.4/9.5
installed on same host instance.  Thanks

-

Posted to Admin a few days ago...  I'll try here next before going to
Hackers or trying to locate the packagers.



Anyone else as enthused as I am about Pg-Logical and tried installing
 both version on same host?

Fails on the output plugin package due to header file conflict.

I recall as might be expected that the reverse is true also.  If you
wipe 9.5 and then install 9.4 it works then you get borked trying to
install the 9.5 version.

I wonder if those headers should be packaged down into a version
numbered directory?




# uname -a
Linux jerry 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64 GNU/Linux

# 
# echo $#; echo $*
4
postgresql-9.4-pglogical postgresql-9.4-pglogical-output 
postgresql-9.5-pglogical postgresql-9.5-pglogical-output

# dpkg -l | grep pglogical
ii  postgresql-9.5-pglogical 1.0.1-1wheezy   amd64  
 PGLogical plugin for PostgreSQL 9.5
ii  postgresql-9.5-pglogical-output  1.0.1-1wheezy   amd64  
 PGLogical Output plugin for PostgreSQL 9.5

# apt-get install -y $1
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following extra packages will be installed:
  postgresql-9.4-pglogical-output
The following NEW packages will be installed:
  postgresql-9.4-pglogical postgresql-9.4-pglogical-output
0 upgraded, 2 newly installed, 0 to remove and 308 not upgraded.
Need to get 0 B/249 kB of archives.
After this operation, 827 kB of additional disk space will be used.
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend will not work on a dumb terminal, an emacs shell 
buffer, or without a controlling terminal.)
debconf: falling back to frontend: Readline
(Reading database ... 173140 files and directories currently installed.)
Unpacking postgresql-9.4-pglogical-output (from 
.../postgresql-9.4-pglogical-output_1.0.1-1wheezy_amd64.deb) ...
dpkg: error processing 
/var/cache/apt/archives/postgresql-9.4-pglogical-output_1.0.1-1wheezy_amd64.deb 
(--unpack):

 trying to overwrite '/usr/include/postgresql/pglogical_output/compat.h', which 
is also in package postgresql-9.5-pglogical-output 1.0.1-1wheezy


Selecting previously unselected package postgresql-9.4-pglogical.
Unpacking postgresql-9.4-pglogical (from 
.../postgresql-9.4-pglogical_1.0.1-1wheezy_amd64.deb) ...
Processing triggers for postgresql-common ...
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend will not work on a dumb terminal, an emacs shell 
buffer, or without a controlling terminal.)
debconf: falling back to frontend: Readline
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
Errors were encountered while processing:
 /var/cache/apt/archives/postgresql-9.4-pglogical-output_1.0.1-1wheezy_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

# dpkg -L $4
/.
/usr
/usr/share
/usr/share/postgresql
/usr/share/postgresql/9.5
/usr/share/postgresql/9.5/extension
/usr/share/postgresql/9.5/extension/pglogical_output--1.0.0.sql
/usr/share/postgresql/9.5/extension/pglogical_output--1.0.1.sql
/usr/share/postgresql/9.5/extension/pglogical_output--1.0.0--1.0.1.sql
/usr/share/postgresql/9.5/extension/pglogical_output.control
/usr/share/doc
/usr/share/doc/postgresql-9.5-pglogical-output
/usr/share/doc/postgresql-9.5-pglogical-output/copyright
/usr/share/doc/postgresql-9.5-pglogical-output/changelog.Debian.gz
/usr/share/doc/postgresql-9.5-pglogical-output/README.md.gz
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/9.5
/usr/lib/postgresql/9.5/lib
/usr/lib/postgresql/9.5/lib/pglogical_output.so
/usr/include
/usr/include/postgresql
/usr/include/postgresql/pglogical_output

/usr/include/postgresql/pglogical_output/compat.h
^^

/usr/include/postgresql/pglogical_output/hooks.h

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal: SET ROLE hook

2015-10-16 Thread Jerry Sievers
Joe Conway <m...@joeconway.com> writes:

> In many environments there is a policy requiring users to login using
> unprivileged accounts, and then escalate their privileges if and when
> they require it. In PostgreSQL this could be done by granting the
> superuser role to an unprivileged user with noinherit, and then making
> the superuser role nologin. Something like:
>
> 8<-
> psql -U postgres
> create user joe noinherit;
> grant postgres to joe;
> alter user postgres nologin;

A bit off-topic, but I've often had to create an intermediate role with
no-inherit between the gifted role to permit accessing it without the
grantee losing ability to inherit some other(s).

To wit;

We have an owner role for a given DB who is supposed to own most/all
objects.  We might want 1 or more other roles/users to be able to run as
owner guy to create objects...  We want to enforce that  objects so
created are automatically owned by the correct owner role.

create role owner;
create role owner_role no inherit in role owner;
create role read_write;
create role jerry in role read_write, owner_role;

I can by default run as the read_write guy and mangle data to my little
heart's content (the super-dev that I am) but if a new table is needs
creation I must do set role owner.  This insures that the table is owned
by owner and not jerry afterward.

Often wondered why not;

grant owner to jerry no inherit;

FWIW

> psql -U joe
> -- do stuff *not requiring* escalated privs
> set role postgres;
> -- do stuff *requiring* escalated privs
> reset role;
> 8<-
>
> One of the problems with this is we would ideally like to know whenever
> joe escalates himself to postgres. Right now that is not really possible
> without doing untenable things such as logging every statement.
>
> In order to address this issue, I am proposing a new SET ROLE hook. The
> attached patch (role-esc-hook.diff) is I believe all we need. Then
> extension authors could implement logging of privilege escalation.
>
> A proof of concept extension patch is also attached. That one is not
> meant to be applied, just illustrates one potential use of the hook. I
> just smashed it on top of passwordcheck for the sake of convenience.
>
> With both patches applied, the following scenario:
> 8<
> psql -U joe postgres
> psql (9.6devel)
> Type "help" for help.
>
> postgres=> set role postgres;
> SET
> postgres=# select rolname, rolpassword from pg_authid;
>  rolname  | rolpassword
> --+-
>  joe  |
>  postgres |
> (2 rows)
>
> postgres=# set log_statement = none;
> SET
> postgres=# reset role;
> RESET
> 8<
>
> Generates the following in the log:
>
> 8<
> LOG:  Role joe transitioning to Superuser Role postgres
> STATEMENT:  set role postgres;
> LOG:  statement: select rolname, rolpassword from pg_authid;
> LOG:  statement: set log_statement = none;
> LOG:  Superuser Role postgres transitioning to Role joe
> STATEMENT:  reset role;
> 8<
>
> Note that we cannot prevent joe from executing
>   set log_statement = none;
> but we at least get the evidence in the log and can ask joe why he felt
> the need to do that. We could also set up alerts based on the logged
> events, etc.
>
> This particular hook will not capture role changes due to SECURITY
> DEFINER functions, but I think that is not only ok but preferred.
> Escalation during a SECURITY DEFINER function is a preplanned sanctioned
> event, unlike an ad hoc unconstrained role change to superuser. And
> given the demo patch, we could see any SECURITY DEFINER function created
> by the superuser when it gets created in the log (which again is subject
> to auditing, alerts, etc.)
>
> Ultimately I would also like to see a general hook available which would
> fire for all changes to GUC settings, but I think this one is still very
> useful as it is highly targeted.
>
> Comments?
>
> Thanks,
>
> Joe

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Changing ownership of simple composite incomplete?

2015-03-26 Thread Jerry Sievers
Hackers;

I spotted this after doing some owner reassignments and then dropping
the old roles.

It looks like using either reassign or alter type owner to, the pg_class
entry stays with old rolID.

Then if we drop that old role  going forward pg_dump complains about
invalid owner.

See below test case.  I did look at releast notes above 9.4 and didn't
notice a fix.  I observed the problem originally on a 9.1 system here.

And the warning is a bit confusing since it says table rather than type.

FYI  Thanks





$ cat q
\set ON_ERROR_STOP

begin;

select version();

create role foo;
create schema foo;
set search_path to foo;

prepare foo as
select c.relowner, t.typowner
from pg_class c
join pg_type t on typrelid = c.oid
and typname = 'foo';

create type foo as (a int);

execute foo;

alter type foo owner to foo;

execute foo;

reassign owned by foo to postgres;
drop role foo;

execute foo;

alter type foo owner to postgres;

execute foo;

commit;

\! pg_dump --schema-only --schema foo /dev/null
\echo '\n\n\n'

drop schema foo cascade;




$ psql -fq
SET
BEGIN
   version  
  
--
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 
4.7.2, 64-bit
(1 row)

CREATE ROLE
CREATE SCHEMA
SET
PREPARE
CREATE TYPE
 relowner | typowner 
--+--
16387 |16387
(1 row)

ALTER TYPE
 relowner | typowner 
--+--
   266324 |   266324
(1 row)

REASSIGN OWNED
DROP ROLE
 relowner | typowner 
--+--
   266324 |   10
(1 row)

ALTER TYPE
 relowner | typowner 
--+--
   266324 |   10
(1 row)

COMMIT
pg_dump: WARNING: owner of table foo appears to be invalid




psql:q:39: NOTICE:  drop cascades to type foo
DROP SCHEMA

$ $ 
-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Changing ownership of simple composite incomplete?

2015-03-26 Thread Jerry Sievers
Jerry Sievers gsiever...@comcast.net writes:

 Hackers;

 I spotted this after doing some owner reassignments and then dropping
 the old roles.

 It looks like using either reassign or alter type owner to, the pg_class
 entry stays with old rolID.

 Then if we drop that old role  going forward pg_dump complains about
 invalid owner.

 See below test case.  I did look at releast notes above 9.4 and didn't
 notice a fix.  I observed the problem originally on a 9.1 system here.

Oop!  I looked at release notes above 9.3.4...  FYI


 And the warning is a bit confusing since it says table rather than type.

 FYI  Thanks





 $ cat q
 \set ON_ERROR_STOP

 begin;

 select version();

 create role foo;
 create schema foo;
 set search_path to foo;

 prepare foo as
 select c.relowner, t.typowner
 from pg_class c
 join pg_type t on typrelid = c.oid
 and typname = 'foo';

 create type foo as (a int);

 execute foo;

 alter type foo owner to foo;

 execute foo;

 reassign owned by foo to postgres;
 drop role foo;

 execute foo;

 alter type foo owner to postgres;

 execute foo;

 commit;

 \! pg_dump --schema-only --schema foo /dev/null
 \echo '\n\n\n'

 drop schema foo cascade;




 $ psql -fq
 SET
 BEGIN
version
 
 --
  PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
 4.7.2-5) 4.7.2, 64-bit
 (1 row)

 CREATE ROLE
 CREATE SCHEMA
 SET
 PREPARE
 CREATE TYPE
  relowner | typowner 
 --+--
 16387 |16387
 (1 row)

 ALTER TYPE
  relowner | typowner 
 --+--
266324 |   266324
 (1 row)

 REASSIGN OWNED
 DROP ROLE
  relowner | typowner 
 --+--
266324 |   10
 (1 row)

 ALTER TYPE
  relowner | typowner 
 --+--
266324 |   10
 (1 row)

 COMMIT
 pg_dump: WARNING: owner of table foo appears to be invalid
 



 psql:q:39: NOTICE:  drop cascades to type foo
 DROP SCHEMA

 $ $ 

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jerry Sievers
Joshua D. Drake j...@commandprompt.com writes:

 On 03/21/2015 12:45 PM, Gavin Flower wrote:


 How about 2 config files?

 One marked adult^H^H^H^H^H power users only, or some such, with the
 really dangerous or unusual options?


 That has come up before in many threads. I don't know that we need to
 go down that path again. Consider, power users don't need a separate
 config. They can create their own or use alter system.


10 years ago I was already feeling like postgresql.conf was unwieldy
and have on many occasions stripped them down only to live lines without
any comments whatsoever.

In fact I'd argue that due to  how verbose the file is presently as
shipped, it makes it that much more likely  that someone looking at is
isn't going to notice  something as alarming as fsync=off :-)

 JD


 -- 
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Support, Training, Professional Services and Development
 High Availability, Oracle Conversion, @cmdpromptinc

 Now I get it: your service is designed for a customer
 base that grew up with Facebook, watches Japanese seizure
 robot anime, and has the attention span of a gnat.
 I'm not that user., Tyler Riddle

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Small bug on CREATE EXTENSION pgq...

2015-01-28 Thread Jerry Sievers
Hackers; I noticed this trying to import a large pg_dump file with
warnings supressed.

It seems loading pgq sets client_min_messages to warning and leaves it
this way which defeats an attempt to change the setting prior and have
it stick.

I tested with several other extensions in same DB and only pgq has the
problem.

Sorry if this is known/fixed already.

Thanks


sj$ cat q
select version();

create database foo template template0;
\c foo

show client_min_messages;

create extension pgq;

show client_min_messages;
reset client_min_messages;
show client_min_messages;

create extension pgq_node;

show client_min_messages;

\c postgres

drop database foo;


sj$ /usr/local/postgresql-9.3/bin/psql -ef q  --no-psqlrc
select version();
   version  
  
--
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 
4.7.2, 64-bit
(1 row)

create database foo template template0;
CREATE DATABASE
psql (9.3.5, server 9.3.4)
You are now connected to database foo as user jsievers.
show client_min_messages;
 client_min_messages 
-
 notice
(1 row)

create extension pgq;
CREATE EXTENSION
show client_min_messages;
 client_min_messages 
-
 warning
(1 row)

reset client_min_messages;
RESET
show client_min_messages;
 client_min_messages 
-
 notice
(1 row)

create extension pgq_node;
CREATE EXTENSION
show client_min_messages;
 client_min_messages 
-
 notice
(1 row)

psql (9.3.5, server 9.3.4)
You are now connected to database postgres as user jsievers.
drop database foo;
DROP DATABASE
sj$ 
-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Append to a GUC parameter ?

2014-08-07 Thread Jerry Sievers
Alvaro Herrera alvhe...@2ndquadrant.com writes:

 Fabrízio de Royes Mello wrote:

 On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 
  Josh Berkus j...@agliodbs.com writes:
   BTW, while there's unlikely to be a good reason to put search_path in
   pg.conf with appends, there are a LOT of reasons to want to be able to
   append to it during a session.
 
  [shrug...]  You can do that today with current_setting()/set_config().
 
 With a very simple statement you can do that:

 Of course, this doesn't solve the requirement that started this thread,
 which is about having includeable pg.conf fragments to enable
 extensions.
o
ISTM the idea of a token in the value string that would expand to an
existing setting s/b general purpose enough to  allow for prepend/append
and not require adding a new opperator as += or whatever.

I say this without knowing just exactly what the implementation effort
is but just to reiterate the original intent.

I think someone already suggest this upthread.

shared_preload_libraries = '%,more_libs'
shared_preload_libraries = 'more_libs,%'

At conclusion of file processing, stripping off an unnecessary delimiter
at beginning or end of string would be a nice asthetic feature and/or
might be required depending on whether an empty list value is legal.

Thanks

 -- 
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Append to a GUC parameter ?

2014-08-05 Thread Jerry Sievers
Using the 'include' keyword in postgresql.conf let's us do groovy things
like paste together pieces of  general purpose configs into the working file.

-- But since all we can do is set/reset the parameters the possibility
   of concatenating fragments that use some of the list parameters won't
   work.

To wit; suppose we had a fragment for enabling auto_explain and another
for enabling pg_stat_statements.

Both of these require us to put something in shared_preload_libraries
and both make use of custom_variable_classes.

Just FWIW...  Something like...

shared_preload_libraries += auto_explain

Would do the trick.

I've  never heard this mentioned before so presume not many have
contemplated this.

Comments?

Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Ok to flip pg_constraint.condeferrable on 9.1?

2014-04-01 Thread Jerry Sievers
Hackers;  as per $subject...

We have an FK defined on a table large enough and 24x7  as to make a
redefining of same constraint  a painful solution.

Ran into a case where defining as deferrable initially immediate and
just running one batch job with deferred firing would solve a
concurrency problem that we discovered.

Grabbing a quick exclusive lock on the 2 related tables would not be a
problem if same might help avoid bad side-effects.

Developers are already working on a chunking solution to avoid the
long-running transaction that gave rise to this but I'd like  to
consider this if  it's not risky. 

Comments?

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] HBA files w/include support?

2014-02-14 Thread Jerry Sievers
Tom Lane t...@sss.pgh.pa.us writes:

 Stephen Frost sfr...@snowman.net writes:

 * Tom Lane (t...@sss.pgh.pa.us) wrote:
 In short: I suspect this approach may be fixing the wrong thing.

 I'm curious what you're thinking would be the right thing to fix here?

 I was asking for use-cases so we could figure out what's the right thing ;-)

No doubt this environment I took over managing, of 90 clusters ranging
from tiny to ~3.5TB, some with hundreds of user accounts...  could stand
some massive rejiggering in regards to users/rols and such to go towards
doing it the right way.

That said, trying to roll up the hba files some with over 300 entries
and lots of cases of high duplication among clusters belonging to somne
subset, would be daunting and perhaps invasive.

I realize that gathering up those duplicates into a file common to
whatever group and then having that pulled in as an include is going to
result  in some reordering of the rules  since they are not in a totally
predictable order  presently

And my Jr. DBAs are still handling requests daily to add more hba rules
quite often to more than a single machine but still along the same
groupings mentioned.

Even without retrofitting a good cleanup here, being able to include a
global section at top of all files and at least one group specific file
next then followed by legacy entries and/or items specific to a single
cluster, might save a lot of work.

Thus my idea of using a make file for this and then inspiring the
question about includes.

Great just getting the ball rolling and I respect whatever the concensus
opinion that emerges.

Thx


 The argument about wanting to assemble a pg_hba file from separately
 managed configuration pieces seems to have some merit, but the weak
 spot there is how do you define the search order?  Or are you planning
 to just cross your fingers and hope it doesn't matter too much?

   regards, tom lane


-- 
Jerry Sievers
e: jerry.siev...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] HBA files w/include support?

2014-02-13 Thread Jerry Sievers
Greetings Hackers.

I'm aware of how a pg_hba.conf file can refer to other files for
including @lists of users, etc.

But there is currently no support for being able to pull in entire file
segments as can be done for postgresql.conf via the include directive.

In the environment that I'm managing, we are using a makefile to stick
together a common header with a custom section for any of several
clusters and may extend this further to permit additional includes for
hba rules common to groupings of clusters.

Anyway, please advise.  I don't recall hearing anything like this
discussed.

Has been proposed, discussed and voted down?  Or never mentioned?

Thx

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] HBA files w/include support?

2014-02-13 Thread Jerry Sievers
Alvaro Herrera alvhe...@2ndquadrant.com writes:

 Bruce Momjian wrote:

 On Thu, Feb 13, 2014 at 08:24:27PM -0600, Jerry Sievers wrote:
  Greetings Hackers.
  
  I'm aware of how a pg_hba.conf file can refer to other files for
  including @lists of users, etc.
  
  But there is currently no support for being able to pull in entire file
  segments as can be done for postgresql.conf via the include directive.

 I have never heard of anyone request this.

 On the contrary, I have vague memories that this has been discussed and
 agreed upon; we have just never implemented it.

Interesting and it'd surprise me if no one has ever wanted the feature.


 One issue with this is that pg_hba.conf is order sensitive, which could
 become a trap for the unwary if includes are used carelessly.

Indeed.

The other thing that comes to mind, is that as opposed to
postgresql.conf and the include scenario there... one can do show all or
query from pg_stat_activity just to see what setting they ended up
with. 

I'm not aware of any way to probe what hba rules are loaded at runtime
and thus, debugging hba config changes not really possible.

I presume that a simple scenario involving just 1 level of includes not
too difficult to grok but nested includes sure might be a foot gun
unless there was a way to dump the resulting configs somehow.

Thus pasting hba files together externally a more reliable approach.

Thanks

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Re: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4

2013-05-13 Thread Jerry Sievers
Bruce Momjian br...@momjian.us writes:

 On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:

 On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
  pg.dropped.16 INTEGER /* dummy */,
  pg.dropped.17 INTEGER /* dummy */,
  pg.dropped.18 INTEGER /* dummy */,
  pg.dropped.19 INTEGER /* dummy */,
  pg.dropped.20 INTEGER /* dummy */,
  pg.dropped.21 INTEGER /* dummy */,
  pg.dropped.22 INTEGER /* dummy */,
  pg.dropped.23 INTEGER /* dummy */,
  pg.dropped.24 INTEGER /* dummy */,
  pg.dropped.25 INTEGER /* dummy */,
  pg.dropped.26 INTEGER /* dummy */,
  ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
  pg.dropped.28 INTEGER /* dummy */,
  dr29 character varying(10)
 
 OK, this verifies that the table had a lot of DDL churn.  I have no idea
 how to pursue this further because I am unsure how we are going to
 replicate the operations performed on this table in the past, as you
 mentioned much of this was before your time on the job.
 
 Evan, I suggest you force a toast table on the table by doing:
 
  ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
 
 Then drop the column.  That will create a toast table and will allow
 pg_upgrade to succeed.

 FYI, I did test adding a TEXT column and altering a column to TEXT on
 Postgres 9.1, and both created a toast table.  I am still have no clues
 about what would have caused the missing toast table.

Possibly manual catalog updates to change a varchar(N) to text and
whoopsie!  That may be one explanation.

   Bruce Momjian  br...@momjian.ushttp://momjian.us
   EnterpriseDB http://enterprisedb.com

   + It's impossible for everything to be true. +


 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Postgresql 9.1 replication failing

2011-12-01 Thread Jerry Sievers
Jim Buttafuoco j...@contacttelecom.com writes:

 All,

 I have a large PG 9.1.1 server (over 1TB of data) and replica using log 
 shipping.  I had some hardware issues on the
 replica system and now I am getting the following in my pg_log/* files.  Same 
 2 lines over and over since yesterday.

 2011-12-01 07:46:30 EST  LOG:  restored log file 0001028E00E5 
 from archive
 2011-12-01 07:46:30 EST  LOG:  incorrect resource manager data checksum in 
 record at 28E/E555E1B8

 Anything I can do on the replica or do I have to start over?

INspect that WAL segment or possibly the one immediatly following it
in comparison to another copy if you still have it on the master or a
central WAL repository.

A standby crashing meanwhile copying in a WAL segment and/or synching
one to disk could result in ramdon corruption.

If you have another copy of the segment and does not compare equal to
the one your standby is trying to read, try another copy.

 Finally, I know this is not the correct list, I tried general with no answer.

The admin list is the right one for such a post probably.

HTH

 Thanks
 Jim
 ___

 [cid]

 Jim Buttafuoco
 j...@contacttelecom.com
 603-647-7170 ext. - Office
 603-490-3409 - Cell
 jimbuttafuoco - Skype


-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 305.321.1144

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Jerry Sievers
Andrew Dunstan and...@dunslane.net writes:

 On 07/25/2011 10:31 PM, Jerry Sievers wrote:
 Hackers;

 I just noticed that somewhere between 8.2 and 8.4, an exception is
 raised trying to alter table ONLY some_partition_parent ADD CHECK
 (foo).



 8.4 had this change:

*

  Force child tables to inherit CHECK constraints from parents
  (Alex Hunsaker, Nikhil Sontakke, Tom)

  Formerly it was possible to drop such a constraint from a
  child table, allowing rows that violate the constraint to be
  visible when scanning the parent table. This was deemed
  inconsistent, as well as contrary to SQL standard.


 You're not the only one who occasionally bangs his head against it.

 cheers

 andrew

Thanks Andrew!...  Yeah, I figured it was a documented change but too
lazy tonight to browse release notes :-)

The previous behavior was to me convenient, but I agree, probably lead
to some confusion too.

That our version of partitioning can be overloaded like this though I
think adds power.  A bit of which we lost adding the restrictgion.





-- 
Jerry Sievers
e: jerry.siev...@comcast.net
p: 305.321.1144

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Check constraints on partition parents only?

2011-07-25 Thread Jerry Sievers
Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

I can understand why it makes sense to handle this as an error.

Howeverin practice on a few systems that I used to manage this would
be a problem.

1. I got into the habit of putting CHECK (false) on the parent table
   if it was an always empty base table,

   This is just really documentation indicating that this table can't
   hold rows and of course, having the partition selector trigger
   raise exception if falling through the if/else logic on a new row
   insertion enforces the constraint but is less obvious.

   Ok, so no real problem here.  Just one example.

2. Atypical partitioning implementation where the parent table was for
   initial insert/update of live records in an OLTP system with high
   update/insert ratio.  This table was partitioned retroactively in
   such a way transparent to the application.  The app would
   eventually update a row one final time and set a status field to
   some terminal status, at which time we'd fire a trigger to move the
   row down into a partition.  Record expiry took place periodically
   by dropping a partition and creating a new one.

   In that case, imagine the application user runs with
   sql_inheritance to off and so, sees only the live data which
   resulted in a huge performance boost.  Reporting apps and in fact
   all other users ran with sql_inheritance to on as usual and so, see
   all the data.

   Suppose the status field had several non-terminal values and one or
   a few terminal values.  The differing check constraints on parent
   and child tables made it easy to see the intent and I presume with
   constraint_exclusion set to on, let queries on behalf of regular
   users that had specified a non-terminal state visit only the tiny
   parent table.
   
   Parent might have CHECK (status in (1,2,3)) and children CHECK
   (status = 4).

   I'll assume not many sites are architected this way but #2 here
   shows a more compelling example of why it might be useful to allow
   check constraints added to only a partition parent.

   Comments?

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 305.321.1144

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] time-delayed standbys

2011-04-20 Thread Jerry Sievers
Greg Stark gsst...@mit.edu writes:

 On Wed, Apr 20, 2011 at 3:47 AM, Robert Haas robertmh...@gmail.com wrote:

 While I was out at the MySQL conference last week, I heard that one of
 the forthcoming MySQL features is time-delayed replication:

 Incidentally, this is a popular Oracle feature. It's a poor man's
 flashback and similar to how some filesystems automatically create
 regular snapshots of every home directory so you can get back stuff at
 some arbitrary point in the past.

Yup.

One of the big bosses where I work went asking for this a couple years
ago.  We're multi-platform; Oracle, MySQL, EnterpriseDB, Vertica.

They put a 6 hour delay on the critical Oracle boxes.  NOthing was done
for MySQL or Vertica since no feature support.

My C-foo being rusty, I elected to add more hacks to our home-spun
PYthon version of pg_standby rather than adding the feature to
pg_standby itself.

Been running with delayed WAL apply ever since..  Shipping happens
immediatly on log creation at master and we simply wait for the files to
age the configured time before application.

In a few cases, we have 2 or more standbys off the same prod master.
One in real-time mode and the others lagged.

Thanks all!

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: gsiever...@comcast.net
p: 305.321.1144

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [PATCHES] Writing WAL for relcache invalidation:pg_internal.init

2006-11-02 Thread Jerry Sievers
Tom, Simon et al;  Please clarify.

PostgreSQL 8.1.5 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2

We're getting ready to init a new warm standby instance based on last
night's snapshot of running prod server.  I see a few of these
pg_internal.init files in the cluster as it's being unpacked. 

Same warm standby instance may sit for weeks gobbling up WALs from the
prod server then be finally brought live.

Question;

Is it safe to delete the .init files now (before starting recovery),
or perhaps unconditionally right after going live?

In other words, is there any sure fire preventative measure that I can
apply to guarantee against the fault that started this threadd?


Tom wrote:
 Meanwhile, if you're trying to recover from a PITR backup and it's not
 working, try removing any pg_internal.init files you can find.

Comment above seems to suggest not touching existing pg_internal.init
files unless a problem is seen.


Thanks


Simon Riggs [EMAIL PROTECTED] writes:

 On Wed, 2006-11-01 at 12:05 -0500, Tom Lane wrote:
 
  Simon Riggs [EMAIL PROTECTED] writes:
   Enclose a patch for new WAL records for relcache invalidation.
  
  I don't think this works.  RelationCacheInitFileInvalidate is executed
  post-commit, which means that there's a window between commit and where
  you propose to write the WAL entry.  A crash and restart in that
  interval would leave the catalog changes committed, but not reflected
  into pg_internal.init.
 
 Surely you are pointing out a bug, no?
 
 If a backend did crash, the init file would be wrong and we'd get
 exactly the same wrong relfilenode errors we got after that PITR.
 
 The issue must surely be that the patch isn't wrong per se, just that
 RelationCacheInitFileInvalidate is called too late and that requires an
 additional fix. Are we certain that a crash between commit and
 invalidation will cause a PANIC that takes down the server? Doesn't look
 like its in a critical section to me.
 
  I think we're probably better off to just forcibly remove the init file
  during post-recovery cleanup.  The easiest place to do this might be
  BuildFlatFiles, which has to scan pg_database anyway ...
 
 I can do this - I don't have a problem there, but the above issue just
 occurred to me so I wonder now if its the right thing to do.
 
 PITR will be always-safe but normal operation might not be.
 
 -- 
   Simon Riggs 
   EnterpriseDB   http://www.enterprisedb.com
 
 
 
 ---(end of broadcast)---
 TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
 

-- 
---
Jerry Sievers   305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobilehttp://www.JerrySievers.com/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match