Re: [GENERAL] Possible to create canonicalized range type without being superuser?

On Tue, Jul 5, 2016 at 10:17 PM, Paul A Jungwirth wrote: > db=> create type inetrange; Here is a follow-up question for creating inet ranges. Is there any way to prevent someone from doing this?: db=> select inetrange('1.2.3.4', '2001:0db8::0042::8a2e:0370:7334', '[]');

Re: [GENERAL] Possible to create canonicalized range type without being superuser?

On Tue, Jul 5, 2016 at 3:28 PM, David G. Johnston wrote: > On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane wrote: >> Paul Jungwirth writes: >> > The problem is this (tried on 9.3 and 9.5): >> >> The only other obvious way to deal with this is to allow the canonical >> function to be defined after the r

Re: [GENERAL] Is it possible to use an EVENT TRIGGER to validate a TRIGGER?

Luís Eduardo Oliveira Lizardo wrote: > Hi, > > Is it possible to use an EVENT TRIGGER to validate a TRIGGER definition? > > What I want is to guarantee that the trigger is fired AFTER a STATEMENT, on > INSERT or UPDATE but not on DELETE, like the following example: What you can do with a C langu

Re: [GENERAL] How sync settings or extensions in streaming replication

On Wed, Jul 6, 2016 at 11:37 AM, 苏士涛 wrote: > I am new to PostgreSQL, and i have a question about streaming > replication. If i enable some extension on master with or without > native code(in c), do I need to enable this extension on slave? Or > PostgreSQL sync this automatic. Same to settings in

[GENERAL] How sync settings or extensions in streaming replication

Hi, I am new to PostgreSQL, and i have a question about streaming replication. If i enable some extension on master with or without native code(in c), do I need to enable this extension on slave? Or PostgreSQL sync this automatic. Same to settings in postgresql.conf. Sorry for my pool English --

[GENERAL] Is it possible to use an EVENT TRIGGER to validate a TRIGGER?

Hi, Is it possible to use an EVENT TRIGGER to validate a TRIGGER definition? What I want is to guarantee that the trigger is fired AFTER a STATEMENT, on INSERT or UPDATE but not on DELETE, like the following example: CREATE TRIGGER mytrigger AFTER INSERT OR UPDATE ON mytable FOR EACH STAT

Re: [GENERAL] Possible to create canonicalized range type without being superuser?

On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane wrote: > Paul Jungwirth writes: > > The problem is this (tried on 9.3 and 9.5): > > db=> create type inetrange; > > ERROR: must be superuser to create a base type > > So I'm wondering whether there is any way around this circle without > > being a superu

Re: [GENERAL] pg_dump fundenental question

"less" is much better for opening huge text files in *nix for reading. On 07/05/16 15:13, Christofer C. Bell wrote: On Tue, Jul 5, 2016 at 3:17 PM, Paul Linehan mailto:lineh...@tcd.ie>> wrote: > a good point, but I would prefer NOT to open a 324GB backup file in a text > editor. I can h

Re: [GENERAL] pg_dump fundenental question

On Tue, Jul 5, 2016 at 3:17 PM, Paul Linehan wrote: > > a good point, but I would prefer NOT to open a 324GB backup file in a > text > > editor. I can however cat/less/head/tail the file in Linux. > > Use vi (or flavour thereof) - it doesn't load the entire file in order to > read the contents of

Re: [GENERAL] Possible to create canonicalized range type without being superuser?

Paul Jungwirth writes: > The problem is this (tried on 9.3 and 9.5): > db=> create type inetrange; > ERROR: must be superuser to create a base type > So I'm wondering whether there is any way around this circle without > being a superuser? Hm. It seems like it should be OK to allow ordinary us

[GENERAL] Possible to create canonicalized range type without being superuser?

Hello, I'm trying to create a custom inetrange type. This works: CREATE TYPE inetrange AS RANGE ( subtype = inet, subtype_diff = inet_diff ); But since inet is discrete, not continuous, I'd like to define a canonical function too: CREATE TYPE inetrange AS RANGE (

Re: [GENERAL] pg_dump fundenental question

> a good point, but I would prefer NOT to open a 324GB backup file in a text > editor. I can however cat/less/head/tail the file in Linux. Use vi (or flavour thereof) - it doesn't load the entire file in order to read the contents of lines 1 - 100 (say). Paul... -- Sent via pgsql-general mail

Re: [GENERAL] psql connection option: statement_timeout

Melvin Davidson writes: > On Sun, Jul 3, 2016 at 7:52 PM, Scott Marlowe wrote: > > correction: > > alter user reporting set statement_timemout=60 is handy for users that > should never take a long time to connect. > > should read > > alter user reporting set state

Re: [GENERAL] pg_dump fundenental question

On Wed, 6 Jul 2016, 1:39 a.m. J. Cassidy, wrote: > Francisco, > > appreciate the tips/hints. > > My input (source) DB is 1TB in size, using the options as stated in my > original email (i.e. no compression it would seem) the output file size is > "only" 324GB. > It would be because of indexes d

Re: [GENERAL] pg_dump fundenental question

Thank you all for the information. On 07/05/2016 10:10 AM, J. Cassidy wrote: > Hello Adrian, > > appreciate the prompt reply, thank you. > > As stated in the original email, I want to know whether compression > (whatever level) is on by default (or not) - if I supply NO extra > switches/options

Re: [GENERAL] pg_dump fundenental question

Francisco, appreciate the tips/hints. My input (source) DB is  1TB in size, using the options as stated in my original email (i.e. no compression it would seem) the output file size is "only" 324GB.  I presume all of the formatting/indices have been ommited. As I said before, I can browse the b

Re: [GENERAL] pg_dump fundenental question

On 07/05/2016 10:10 AM, J. Cassidy wrote: Hello Adrian, appreciate the prompt reply, thank you. As stated in the original email, I want to know whether compression (whatever level) is on by default (or not) - if I supply NO extra switches/options. There is no compression by default. -- Com

Re: [GENERAL] pg_dump fundenental question

On Tue, Jul 5, 2016 at 4:54 PM, J. Cassidy wrote: > I have hopefully an "easy" question. > If I issue the pg_dump command with no switches or options i.e. > /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd > Is their any "default" compression involved or not? Does pg_dump talk to >

Re: [GENERAL] pg_dump fundenental question

J. Cassidy wrote: > As stated in the original email, I want to know whether compression > (whatever level) is on by default (or not) -  if I supply NO extra > switches/options. I have read the documentation and it is unclear in > this respect. I am a Mainframer and perhaps have a different world

Re: [GENERAL] pg_dump fundenental question

Rob, appreciate the reply but I have never used nor never will use "that" os (small capitals intentional. Regards, Henk

Re: [GENERAL] pg_dump fundenental question

Hello David, a good point, but I would prefer NOT to open a 324GB backup file in a text editor. I can however cat/less/head/tail the file in Linux. Regards, Henk  

Re: [GENERAL] 9.6 Beta 2 Performance Regression on Recursive CTE

"Nicholson, Brad (Toronto, ON, CA)" writes: > I'm seeing a performance regression on 9.6 Beta 2 compared to 9.5.3. > The query is question is a recursive query on graph data stored as an > adjacency list. FWIW, I can't reproduce any regression with this example. I get EXPLAIN execution times lik

Re: [GENERAL] pg_dump fundenental question

Hello Adrian, appreciate the prompt reply, thank you. As stated in the original email, I want to know whether compression (whatever level) is on by default (or not) -  if I supply NO extra switches/options. I have read the documentation and it is unclear in this respect. I am a Mainframer and

Re: [GENERAL] pg_dump fundenental question

On 07/05/2016 10:54 AM, David G. Johnston wrote: On Tue, Jul 5, 2016 at 10:54 AM, J. Cassidy >wrote: Hello all, I have hopefully an "easy" question. If I issue the pg_dump command with no switches or options i.e. /usr/local/pgsql/bin/pg_dump -v dbab

Re: [GENERAL] pg_dump fundenental question

On Tue, Jul 5, 2016 at 10:54 AM, J. Cassidy wrote: > Hello all, > > I have hopefully an "easy" question. > > If I issue the pg_dump command with no switches or options i.e. > > /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd > > Is their any "default" compression involved or not?

Re: [GENERAL] Materialized view not created with import

On 07/05/2016 06:28 AM, Aurelien Praga wrote: Hello, I need your help because I have a materialized view not created during an import. This materialized view is using a function and this function depends on a table. When importing the database dump: - the function is created - the materialized v

Re: [GENERAL] pg_dump fundenental question

On 07/05/2016 07:54 AM, J. Cassidy wrote: Hello all, I have hopefully an "easy" question. If I issue the pg_dump command with no switches or options i.e. /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd Is their any "default" compression involved or not? Does pg_dump talk to zl

[GENERAL] Materialized view not created with import

Hello, I need your help because I have a materialized view not created during an import. This materialized view is using a function and this function depends on a table. When importing the database dump: - the function is created - the materialized view is not created because the table used by the

[GENERAL] pg_dump fundenental question

Hello all, I have hopefully an "easy" question. If I issue the pg_dump command with no switches or options i.e. /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd Is their any "default" compression involved or not? Does pg_dump talk to zlib during the dump process given that I ha

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

The best that I can do right now is provide the explain of the three variants (see below). The use of a left join did indeed remove the useless joins, but the selected plan is just terrible. Thanks, Igor -Original Message- From: Kevin Grittner [mailto:kgri...@gmail.com] Sent: Saturd

Re: [GENERAL] Avoid deadlocks on alter table

Adrian Klaver writes: > On 07/05/2016 06:30 AM, Christian Castelli wrote: >> ALTER TABLE smartphone >> ADD CONSTRAINT pk_smartphone PRIMARY KEY (id), >> ADD CONSTRAINT fk1 FOREIGN KEY (id_contact) >> REFERENCES contact (id) MATCH SIMPLE >> ON UPDATE RESTRICT ON DELETE RESTRICT, >> ADD CONSTRAINT f

Re: [GENERAL] Avoid deadlocks on alter table

On 07/05/2016 06:30 AM, Christian Castelli wrote: Hi everybody, my database is composed of multiple schemata, one for each customer, and some global views which do UNION across schemata. I create a new customer with a single transaction, with queries like: CREATE TABLE table1 WITHOUT OIDS AS TAB

Re: [GENERAL] How safe is pg_basebackup + continuous archiving?

Thank you all a lot for the detailed answers! :) Kaixi On Thu, Jun 30, 2016 at 3:15 PM Michael Paquier wrote: > On Thu, Jun 30, 2016 at 9:00 PM, Kaixi Luo wrote: > >> Before replaying a backup on a production system, you would need a > >> pre-production setup where the backup is replayed and c

[GENERAL] Avoid deadlocks on alter table

Hi everybody, my database is composed of multiple schemata, one for each customer, and some global views which do UNION across schemata. I create a new customer with a single transaction, with queries like: CREATE TABLE table1 WITHOUT OIDS AS TABLE base_template.table1 WITH NO DATA; base_template

Re: [GENERAL] Cluster on NAS and data center.

Hello, Thanks to emails, we have achieved what we wanted. This is what we’ve done: Compilation: CFLAGS="-mx32 -fexcess-precision=standard -O2" CXXFLAGS="-mx32" ./configure --without-zlib --disable-float8-byval --without-readline --host=x86_64-linux-gnux32 We also have installed libx32. Right