Get attributes names

[ramsiddu007]

Dear all,
  I hope you are all doing well. Today i got one requirement,
for that i need attribute names as below example.

Xml:




In the above sample input xml, i want attributes list of emp_deails like.
Attributes
___
emp_id
emp_name
dept


Thanking you,

Need a command to take the backup of the child tables along with its master table.

[Raghavendra Rao J S V]

Hi All,

We have one master table and multiple child tables (dependent tables)
associated with it.



While taking the backup of the master table , I would like to take the
backup of all its child (dependent) tables backup also.



Please guide me how to take the backup of the  master table and its
dependent tables using *pg_dump *command.

-- 
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Re: Get attributes names

[Arthur Zakirov]

On 24.01.2019 11:54, ramsiddu007 wrote:

Dear all,
               I hope you are all doing well. Today i got one 
requirement, for that i need attribute names as below example.


Xml:




In the above sample input xml, i want attributes list of emp_deails like.
Attributes
___
emp_id
emp_name
dept


Thanking you,


In PostgreSQL 11 (and 10 I think) you can do something like this:

=# CREATE TABLE xmldata AS SELECT xml $$



$$
as data;
=# SELECT xmltable.*
FROM xmldata,
  xmltable('/emp/emp_details/@*' passing data
columns atrr text path 'name()');
   atrr
--
 emp_id
 emp_name
 dept

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Re: Need a command to take the backup of the child tables along with its master table.

[Ron]

On 1/24/19 3:40 AM, Raghavendra Rao J S V wrote:

Hi All,

We have one master table and multiple child tables (dependent tables)  
associated with it.


While taking the backup of the master table , I would like to take the 
backup of all its child (dependent) tables backup also.


Please guide me how to take the backup of the  master table and its 
dependent tables using *pg_dump *command.




Do you know the list of dependent tables?  If so, you can list them in the 
"pg_dump --table=" clause.


If not, you'll have to do some bash magic in parsing the "Foreign-key 
constraints:" clause of "\d your_table_name" and stuff them in the "pg_dump 
--table=" clause.



--
Angular momentum makes the world go 'round.

Re: Get attributes names

[ramsiddu007]

Hi Arthur,
  Thanks for your help. But am using postgres 9.6 version.

On Thu, 24 Jan 2019, 15:24 Arthur Zakirov  On 24.01.2019 11:54, ramsiddu007 wrote:
> > Dear all,
> >I hope you are all doing well. Today i got one
> > requirement, for that i need attribute names as below example.
> >
> > Xml:
> > 
> > 
> > 
> >
> > In the above sample input xml, i want attributes list of emp_deails like.
> > Attributes
> > ___
> > emp_id
> > emp_name
> > dept
> >
> >
> > Thanking you,
> >
> In PostgreSQL 11 (and 10 I think) you can do something like this:
>
> =# CREATE TABLE xmldata AS SELECT xml $$
> 
> 
> 
> $$
> as data;
> =# SELECT xmltable.*
> FROM xmldata,
>xmltable('/emp/emp_details/@*' passing data
>  columns atrr text path 'name()');
> atrr
> --
>   emp_id
>   emp_name
>   dept
>
> --
> Arthur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>

[pgbackrest] Expiring the last backup?

[Ron]

Hi,

The backups partition is running out of disk space, and I need to delete the 
only backup.


$ pgbackrest expire --stanza=localhost --retention-full=0
ERROR: [032]: '0' is out of range for 'repo1-retention-full' option

So, in version 2.07, what's the secret sauce for deleting that final copy?

(Yes, we're getting more disk space, but that takes time.)

--
Angular momentum makes the world go 'round.

Casting Integer to Boolean in assignment

[Alexandre GRAIL]

Hello the list,

Maybe this question has been debated before (I didn't find anything 
helpful) but :


Why the default is to throw an error when casting Integer to Boolean in 
assignment, and accepting it everywhere else ?


So you can type :

postgres=# select 1::boolean;
 bool
--
 t
(1 row)

or

postgres=# select 0::boolean;
 bool
--
 f
(1 row)


But you *cannot* use 1 or 0 as valid input for boolean type when 
inserting or updating :


test=# CREATE TABLE test1 (a boolean);
CREATE TABLE
test=# INSERT INTO test1 VALUES (1);
ERROR:  column "a" is of type boolean but expression is of type integer
LINE 1: INSERT INTO test1 VALUES (1);
  ^
HINT:  You will need to rewrite or cast the expression.


This behavior cannot be changed, as this cast is hard coded with 
"Implicit?=no".


And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

So is there a reason to forbid 0 and 1 as valid boolean, without 
explicit cast ?


Thanks!

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 11:04, Alexandre GRAIL
 wrote:
>
> And added to this weirdness is the fact that '1' or '0' (with quote) is OK.
>
The reason for that at least is that '1' and '0' are valid boolean values.

https://www.postgresql.org/docs/9.5/datatype-boolean.html

There's additional text describing why casts are chosen to be defined
as implicit or not here

https://www.postgresql.org/docs/9.5/typeconv-overview.html

My own opinion is that non-0 should implicitly cast as true and 0
should cast as false. I just run

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
 SELECT c.oid
 FROM pg_cast c
 inner join pg_type src ON src.oid = c.castsource
 inner join pg_type tgt ON tgt.oid = c.casttarget
 WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
 OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%')
 OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%')
 OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%')
 OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int')
);

when I install the system to solve this for my own uses.

Geoff

Re: Casting Integer to Boolean in assignment

[Thomas Kellerer]

Geoff Winkless schrieb am 24.01.2019 um 12:45:
> The reason for that at least is that '1' and '0' are valid boolean values.
> 
> https://www.postgresql.org/docs/9.5/datatype-boolean.html
> 
> There's additional text describing why casts are chosen to be defined
> as implicit or not here
> 
> https://www.postgresql.org/docs/9.5/typeconv-overview.html
> 
> My own opinion is that non-0 should implicitly cast as true and 0
> should cast as false. 

I strongly disagree - that would mimic MySQL's idiosyncrasies and would make 
such a query valid:

  delete from orders
  where 42;

Re: Need a command to take the backup of the child tables along with its master table.

[Francisco Olarte]

On Thu, Jan 24, 2019 at 11:01 AM Ron  wrote:
> If not, you'll have to do some bash magic in parsing the "Foreign-key 
> constraints:" clause of "\d your_table_name" and stuff them in the "pg_dump 
> --table=" clause.

That would be hard. There is an option to make psql show the queries
it uses to implement \d. Reading that  it is normally easy to buld a
query which lists the dependents by creative imitation and feed it to
psql ( instead of the \d ) with the approppiate context setting
options to spit the table names, or even the --table="xx" list,
including schemas and other niceties.

And if his shell is not bash, but CMD.EXE or other similar thing,
parsing \d output will be hell.

Francisco Olarte.

Re: [pgbackrest] Expiring the last backup?

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> The backups partition is running out of disk space, and I need to delete the
> only backup.
> 
> $ pgbackrest expire --stanza=localhost --retention-full=0
> ERROR: [032]: '0' is out of range for 'repo1-retention-full' option

Yeah, pgbackrest doesn't ever really think you want to get rid of *all*
of your backups. :)

> So, in version 2.07, what's the secret sauce for deleting that final copy?

If this is the only backup and the only cluster being backed up into
that repository, you could just nuke the whole repo and recreate it with
stanza-create once you're ready to have backups again..

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 12:17, Thomas Kellerer  wrote:
> Geoff Winkless schrieb am 24.01.2019 um 12:45:
> > My own opinion is that non-0 should implicitly cast as true and 0
> > should cast as false.
>
> I strongly disagree - that would mimic MySQL's idiosyncrasies and would make 
> such a query valid:

Feel free. I said it's my own opinion and gave a way for someone who
agrees with me to do the same as I do.

If your objection is that someone can write a stupid query and it
might go wrong, there are a million other things that should be
addressed before implicit int::bool casts.

Geoff

Re: [pgbackrest] Expiring the last backup?

[Ron]

On 1/24/19 7:26 AM, Stephen Frost wrote:

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:

The backups partition is running out of disk space, and I need to delete the
only backup.

$ pgbackrest expire --stanza=localhost --retention-full=0
ERROR: [032]: '0' is out of range for 'repo1-retention-full' option

Yeah, pgbackrest doesn't ever really think you want to get rid of *all*
of your backups. :)


It's rare, but *is* occasionally needed.


So, in version 2.07, what's the secret sauce for deleting that final copy?

If this is the only backup and the only cluster being backed up into
that repository, you could just nuke the whole repo and recreate it with
stanza-create once you're ready to have backups again..


That occurred to me while lying in bed.  Use "stanza-delete"?

--
Angular momentum makes the world go 'round.

Re: [pgbackrest] Expiring the last backup?

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 1/24/19 7:26 AM, Stephen Frost wrote:
> >* Ron (ronljohnso...@gmail.com) wrote:
> >>The backups partition is running out of disk space, and I need to delete the
> >>only backup.
> >>
> >>$ pgbackrest expire --stanza=localhost --retention-full=0
> >>ERROR: [032]: '0' is out of range for 'repo1-retention-full' option
> >Yeah, pgbackrest doesn't ever really think you want to get rid of *all*
> >of your backups. :)
> 
> It's rare, but *is* occasionally needed.
> 
> >>So, in version 2.07, what's the secret sauce for deleting that final copy?
> >If this is the only backup and the only cluster being backed up into
> >that repository, you could just nuke the whole repo and recreate it with
> >stanza-create once you're ready to have backups again..
> 
> That occurred to me while lying in bed.  Use "stanza-delete"?

That's another option, though you'll have to use --force unless you're
also shutting PG down.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Casting Integer to Boolean in assignment

[Ron]

On 1/24/19 5:04 AM, Alexandre GRAIL wrote:

Hello the list,

Maybe this question has been debated before (I didn't find anything 
helpful) but :


Why the default is to throw an error when casting Integer to Boolean in 
assignment, and accepting it everywhere else ?


So you can type :

postgres=# select 1::boolean;
 bool
--
 t
(1 row)

or

postgres=# select 0::boolean;
 bool
--
 f
(1 row)


But you *cannot* use 1 or 0 as valid input for boolean type when inserting 
or updating :


test=# CREATE TABLE test1 (a boolean);
CREATE TABLE
test=# INSERT INTO test1 VALUES (1);
ERROR:  column "a" is of type boolean but expression is of type integer
LINE 1: INSERT INTO test1 VALUES (1);
  ^
HINT:  You will need to rewrite or cast the expression.


This behavior cannot be changed, as this cast is hard coded with 
"Implicit?=no".


And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

So is there a reason to forbid 0 and 1 as valid boolean, without explicit 
cast ?


I'm confused at the question.  You are happily casting 1 and 0 to boolean in 
the SELECT statements, and then grumbling when *not* casting them in the 
INSERT statements.  Thus, why aren't you casting during the INSERT statements?


test=# INSERT INTO test1 VALUES (1::boolean);


--
Angular momentum makes the world go 'round.

How duplicate values inserted into the primary key column of table and how to fix it

[Raghavendra Rao J S V]

Hi All,


We are using Postgresql 9.2 database.



In one of the transactional table, I have observed duplicate values for the
primary key columns.



Please guide me how is it possible and how to fix this kind of issue.

-- 
Regards,
Raghavendra Rao J S V

Postgres 11 chooses seq scan instead of index-only scan

[twoflower]

I restored a dump of our production DB (running on 9.6) to a Postgres 11
server and wanted to run some basic benchmarks to see if there isn't some
unexpected performance drop.

One issue I cannot resolve is the new server using a parallel seq scan
instead of index-only scan for the following query:

select count(id) from history_translation

The table has about 123 million rows. The servers use identical
configuration. The hardware is similar (4 cores and 18 GB RAM for the 9.6
server vs. 26 GB RAM for the new one). In particular, all the *_cost
settings have the default value and the only possibly relevant settings with
non-default value are
shared_buffers = 2048MBwork_mem = 32MB

The query finishes in *39 seconds* on the 9.6 server and in *2 minutes* on
the 11 server.

Even when I effectively disable parallel queries (using set
max_parallel_workers_per_gather = 0), the new server chooses sequential scan
and, of course, takes much longer to finish the query. I tried recreating
the index and analyzing the table again, but it did not change anything.

Any help will be welcome.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

log_min_duration_statement

[bhargav kamineni]

Hi Team,,

I have set log_min_duration_statement=1000 as my configuration but when i
observe log file i could see queries that ran below 1000ms are also getting
logged , for instance
duration: 0.089 ms
 duration: 0.175 ms
 duration: 0.139 ms
 duration: 0.451 ms
duration: 0.136 ms
 duration: 0.340 ms
 duration: 0.150 ms
duration: 0.440 ms
duration: 0.338 ms
 duration: 0.156 ms
tion: 0.396 ms
 duration: 0.221 ms
duration: 0.161 ms
 *
these are my logsettings

  name   |  setting
-+---
 log_autovacuum_min_duration | 0
 log_checkpoints | on
 log_connections | on
 log_destination | syslog
 log_directory   | log
 log_disconnections  | on
 log_duration| off
 log_error_verbosity | default
 log_executor_stats  | off
 log_file_mode   | 0600
 log_filename| postgresql-%Y-%m-%d_%H%M%S.log
 log_hostname| off
 log_line_prefix |
appname=%a,user=%u,db=%d,pid=%p,txid=%x,vtxid=%v
 log_lock_waits  | on
 log_min_duration_statement  | 1000
 log_min_error_statement | error
 log_min_messages| error
 log_parser_stats| off
 log_planner_stats   | off
 log_replication_commands| off
 log_rotation_age| 1440
 log_rotation_size   | 10240
 log_statement   | all
 log_statement_stats | off
 log_temp_files  | 0
 log_timezone| Navajo
 log_truncate_on_rotation| off
 logging_collector   | on
 max_logical_replication_workers | 4
 syslog_facility | local4
 syslog_ident| postgres
 syslog_sequence_numbers | on
 syslog_split_messages   | on
 wal_log_hints   | off

Regards,
Bhargav K

Re: Casting Integer to Boolean in assignment

[David G. Johnston]

On Thu, Jan 24, 2019 at 4:04 AM Alexandre GRAIL
 wrote:
> But you *cannot* use 1 or 0 as valid input for boolean type when
> inserting or updating :
>
> test=# CREATE TABLE test1 (a boolean);
> CREATE TABLE
> test=# INSERT INTO test1 VALUES (1);
> ERROR:  column "a" is of type boolean but expression is of type integer
> LINE 1: INSERT INTO test1 VALUES (1);
>^
> HINT:  You will need to rewrite or cast the expression.
>
>
> This behavior cannot be changed, as this cast is hard coded with
> "Implicit?=no".
>
> And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

Because '1' is the literal character 1 with an unknown type and so can
be (must be) assigned its initial real type from context.

1 is an integer, which is a real type

> So is there a reason to forbid 0 and 1 as valid boolean, without
> explicit cast ?

To assist developers in avoiding the writing of buggy queries.

David J.

Re: How duplicate values inserted into the primary key column of table and how to fix it

[Hellmuth Vargas]

Hi
you could provide the definition of the table that includes the definition
of the primary key?

El jue., 24 de ene. de 2019 a la(s) 09:18, Raghavendra Rao J S V (
raghavendra...@gmail.com) escribió:

> Hi All,
>
>
> We are using Postgresql 9.2 database.
>
>
>
> In one of the transactional table, I have observed duplicate values for
> the primary key columns.
>
>
>
> Please guide me how is it possible and how to fix this kind of issue.
>
> --
> Regards,
> Raghavendra Rao J S V
>
>

-- 
Cordialmente,

Ing. Hellmuth I. Vargas S.

Re: Postgres 11 chooses seq scan instead of index-only scan

[Tom Lane]

twoflower  writes:
> One issue I cannot resolve is the new server using a parallel seq scan
> instead of index-only scan for the following query:
> select count(id) from history_translation

You might need to vacuum the table to ensure that the planner thinks
a reasonable proportion of the pages are all-visible (see
pg_class.relallvisible).

regards, tom lane

Re: Postgres 11 chooses seq scan instead of index-only scan

[twoflower]

Yes! That was it, after running VACUUM TABLE history_translation, the query
is now executed using index-only scan.

I was under the impression that ANALYZE TABLE history_translation is enough,
but it is not.

Thank you very much.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 14:28, David G. Johnston
 wrote:
> To assist developers in avoiding the writing of buggy queries.

Amazing how many of these developers find this a hindrance. If only
they could see how helpful we're being to them.

Geoff

Re: Postgres 11 chooses seq scan instead of index-only scan

[Don Seiler]

On Thu, Jan 24, 2019 at 9:01 AM twoflower  wrote:

> Yes! That was it, after running VACUUM TABLE history_translation, the
> query is now executed using index-only scan.
>
> I was under the impression that ANALYZE TABLE history_translation is
> enough, but it is not.
>

Only a VACUUM will update the visibility map.
https://www.postgresql.org/docs/current/storage-vm.html

I used to think the same, that ANALYZE was enough, coming from an Oracle
background. I learned later that the visibility map isn't just used to
determine what to vacuum, but it is used by the optimizer/planner when
evaluating execution plans.

-- 
Don Seiler
www.seiler.us

Re: [pgbackrest] Expiring the last backup?

[Ron]

On 1/24/19 8:11 AM, Stephen Frost wrote:

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:

On 1/24/19 7:26 AM, Stephen Frost wrote:

* Ron (ronljohnso...@gmail.com) wrote:

The backups partition is running out of disk space, and I need to delete the
only backup.

$ pgbackrest expire --stanza=localhost --retention-full=0
ERROR: [032]: '0' is out of range for 'repo1-retention-full' option

Yeah, pgbackrest doesn't ever really think you want to get rid of *all*
of your backups. :)

It's rare, but *is* occasionally needed.


So, in version 2.07, what's the secret sauce for deleting that final copy?

If this is the only backup and the only cluster being backed up into
that repository, you could just nuke the whole repo and recreate it with
stanza-create once you're ready to have backups again..

That occurred to me while lying in bed.  Use "stanza-delete"?

That's another option,


What's the preferred method?


though you'll have to use --force unless you're also shutting PG down.


Won't be stopping postgres!!!

Are these the steps, or am I missing something?

$ pgbackrest stop
$ pgbackrest stanza-delete --stanza=mystanza--force



--
Angular momentum makes the world go 'round.

Re: Casting Integer to Boolean in assignment

[Ron]

On 1/24/19 9:05 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 14:28, David G. Johnston
 wrote:

To assist developers in avoiding the writing of buggy queries.

Amazing how many of these developers find this a hindrance. If only
they could see how helpful we're being to them.


It's the C vs. Ada/Pascal debate, 35 years later...

--
Angular momentum makes the world go 'round.

Re: Casting Integer to Boolean in assignment

[Tom Lane]

Geoff Winkless  writes:
> On Thu, 24 Jan 2019 at 14:28, David G. Johnston
>  wrote:
>> To assist developers in avoiding the writing of buggy queries.

> Amazing how many of these developers find this a hindrance. If only
> they could see how helpful we're being to them.

People don't generally post to the lists after a type-mismatch error
catches a typo for them.  So it's pretty hard to tell about "how
many" developers would find one behavior more useful than the other.
It is safe to say, though, that the same developer complaining today
might have their bacon saved tomorrow.

regards, tom lane

Re: [pgbackrest] Expiring the last backup?

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 1/24/19 8:11 AM, Stephen Frost wrote:
> >* Ron (ronljohnso...@gmail.com) wrote:
> >>On 1/24/19 7:26 AM, Stephen Frost wrote:
> >>>* Ron (ronljohnso...@gmail.com) wrote:
> The backups partition is running out of disk space, and I need to delete 
> the
> only backup.
> 
> $ pgbackrest expire --stanza=localhost --retention-full=0
> ERROR: [032]: '0' is out of range for 'repo1-retention-full' option
> >>>Yeah, pgbackrest doesn't ever really think you want to get rid of *all*
> >>>of your backups. :)
> >>It's rare, but *is* occasionally needed.
> >>
> So, in version 2.07, what's the secret sauce for deleting that final copy?
> >>>If this is the only backup and the only cluster being backed up into
> >>>that repository, you could just nuke the whole repo and recreate it with
> >>>stanza-create once you're ready to have backups again..
> >>That occurred to me while lying in bed.  Use "stanza-delete"?
> >That's another option,
> 
> What's the preferred method?

Not sure that I have one..

> >though you'll have to use --force unless you're also shutting PG down.
> 
> Won't be stopping postgres!!!
> 
> Are these the steps, or am I missing something?
> 
> $ pgbackrest stop

This probably isn't really necessary.

> $ pgbackrest stanza-delete --stanza=mystanza--force

Well, you'd need a space before --force..

You'll also probably want to adjust your archive_command because
otherwise it'll start failing and that would lead to WAL building up.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: log_min_duration_statement

[Adrian Klaver]

On 1/24/19 6:23 AM, bhargav kamineni wrote:

Hi Team,,

I have set log_min_duration_statement=1000 as my configuration but when 
i observe log file i could see queries that ran below 1000ms are also 
getting logged , for instance

duration: 0.089 ms
  duration: 0.175 ms
  duration: 0.139 ms
  duration: 0.451 ms
duration: 0.136 ms
  duration: 0.340 ms
  duration: 0.150 ms
duration: 0.440 ms
duration: 0.338 ms
  duration: 0.156 ms
tion: 0.396 ms
  duration: 0.221 ms
duration: 0.161 ms
  *


The above would not happen to be autovacuum statements?

You have log_autovacuum_min_duration = 0 below.


these are my logsettings

               name               |                      setting
-+---
  log_autovacuum_min_duration     | 0
  log_checkpoints                 | on
  log_connections                 | on
  log_destination                 | syslog
  log_directory                   | log
  log_disconnections              | on
  log_duration                    | off
  log_error_verbosity             | default
  log_executor_stats              | off
  log_file_mode                   | 0600
  log_filename                    | postgresql-%Y-%m-%d_%H%M%S.log
  log_hostname                    | off
  log_line_prefix                 | 
appname=%a,user=%u,db=%d,pid=%p,txid=%x,vtxid=%v

  log_lock_waits                  | on
  log_min_duration_statement      | 1000
  log_min_error_statement         | error
  log_min_messages                | error
  log_parser_stats                | off
  log_planner_stats               | off
  log_replication_commands        | off
  log_rotation_age                | 1440
  log_rotation_size               | 10240
  log_statement                   | all
  log_statement_stats             | off
  log_temp_files                  | 0
  log_timezone                    | Navajo
  log_truncate_on_rotation        | off
  logging_collector               | on
  max_logical_replication_workers | 4
  syslog_facility                 | local4
  syslog_ident                    | postgres
  syslog_sequence_numbers         | on
  syslog_split_messages           | on
  wal_log_hints                   | off

Regards,
Bhargav K





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Casting Integer to Boolean in assignment

[Adrian Klaver]

On 1/24/19 3:04 AM, Alexandre GRAIL wrote:

Hello the list,

Maybe this question has been debated before (I didn't find anything 
helpful) but :


Why the default is to throw an error when casting Integer to Boolean in 
assignment, and accepting it everywhere else ?


The overall reason:

https://www.postgresql.org/docs/8.3/release-8-3.html

E.24.2.1. General

Non-character data types are no longer automatically cast to TEXT 
(Peter, Tom)


Previously, if a non-character value was supplied to an operator or 
function that requires text input, it was automatically cast to text, 
for most (though not all) built-in data types. This no longer happens: 
an explicit cast to text is now required for all non-character-string 
types. For example, these expressions formerly worked:




So you can type :

postgres=# select 1::boolean;
  bool
--
  t
(1 row)

or

postgres=# select 0::boolean;
  bool
--
  f
(1 row)


But you *cannot* use 1 or 0 as valid input for boolean type when 
inserting or updating :


test=# CREATE TABLE test1 (a boolean);
CREATE TABLE
test=# INSERT INTO test1 VALUES (1);
ERROR:  column "a" is of type boolean but expression is of type integer
LINE 1: INSERT INTO test1 VALUES (1);
   ^
HINT:  You will need to rewrite or cast the expression.


This behavior cannot be changed, as this cast is hard coded with 
"Implicit?=no".


And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

So is there a reason to forbid 0 and 1 as valid boolean, without 
explicit cast ?


Thanks!







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 15:11, Tom Lane  wrote:
> People don't generally post to the lists after a type-mismatch error
> catches a typo for them.  So it's pretty hard to tell about "how
> many" developers would find one behavior more useful than the other.
> It is safe to say, though, that the same developer complaining today
> might have their bacon saved tomorrow.

I've missed off WHERE clauses on a live database (oops) in my time,
and I'm happy to see work being done to safeguard against that
(although I tend to be of the opinion that it's not something you ever
do twice!) but I can confidently state that I've never once been
caught out by being surprised that a number was treated as a boolean.

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.

But I have been caught out by boolean vs int, enough that I bothered
to search out that ALTER statement. And I'm a lazy person at heart, so
if something irritated me enough to bother doing that, you can be sure
it was _really_ irritating me.

Geoff

Re: Casting Integer to Boolean in assignment

[Adrian Klaver]

On 1/24/19 7:21 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 15:11, Tom Lane  wrote:

People don't generally post to the lists after a type-mismatch error
catches a typo for them.  So it's pretty hard to tell about "how
many" developers would find one behavior more useful than the other.
It is safe to say, though, that the same developer complaining today
might have their bacon saved tomorrow.


I've missed off WHERE clauses on a live database (oops) in my time,
and I'm happy to see work being done to safeguard against that
(although I tend to be of the opinion that it's not something you ever
do twice!) but I can confidently state that I've never once been
caught out by being surprised that a number was treated as a boolean.

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.


create table delete_test(id integer);

insert into delete_test values (2), (3), (4);

delete from delete_test where 1::boolean; 



DELETE 3

select * from delete_test ; 



 id 



 



(0 rows)



But I have been caught out by boolean vs int, enough that I bothered
to search out that ALTER statement. And I'm a lazy person at heart, so
if something irritated me enough to bother doing that, you can be sure
it was _really_ irritating me.

Geoff





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How duplicate values inserted into the primary key column of table and how to fix it

[Adrian Klaver]

On 1/24/19 4:46 AM, Raghavendra Rao J S V wrote:

Hi All,


We are using Postgresql 9.2 database.

In one of the transactional table, I have observed duplicate values for 
the primary key columns.


Best guess a corrupted index. As Hellmuth said more information would be 
helpful.




Please guide me how is it possible and how to fix this kind of issue.


--
Regards,
Raghavendra Rao J S V




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 15:28, Adrian Klaver  wrote:
> On 1/24/19 7:21 AM, Geoff Winkless wrote:
> > How could you even write a query like the one Thomas posted? It
> > doesn't even look remotely sensible.

> delete from delete_test where 1::boolean;

*chuckle*

You misunderstand me.

I mean, how can one write a query like that by mistake?

DELETE FROM  WHERE ;

What would you be thinking that that ought to do?

G

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 15:32, Geoff Winkless  wrote:
> DELETE FROM  WHERE ;
>
> What would you be thinking that that ought to do?

To be fair, I suppose that accidentally missing out a test but
including an integer field

DELETE FROM  WHERE ;

could do this. Not something I've ever done, but at least I see how
it's possible.

*shrug* I should reiterate, it's just my opinion, I'm certainly not
arguing for it to be changed, although I would be pretty upset if the
existing ability to change the behaviour were removed.

Geoff

Re: Casting Integer to Boolean in assignment

[Adrian Klaver]

On 1/24/19 7:32 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 15:28, Adrian Klaver  wrote:

On 1/24/19 7:21 AM, Geoff Winkless wrote:

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.



delete from delete_test where 1::boolean;


*chuckle*

You misunderstand me.

I mean, how can one write a query like that by mistake >
DELETE FROM  WHERE ;

What would you be thinking that that ought to do?


Getting in a hurry/distracted.

I wrote out the above test case in psql and with tab completion it is 
easy to get to:


delete from delete_test where

and then forget the 'field =' part. Though my more common mistake along 
that line is:


delete from delete_test;

At any rate, if it can be done it will be done.



G




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 15:40, Adrian Klaver  wrote:
> delete from delete_test where
>
> and then forget the 'field =' part. Though my more common mistake along
> that line is:
>
> delete from delete_test;
>
> At any rate, if it can be done it will be done.

If you follow that logic, then having a single boolean test at all
should be invalid.

CREATE TABLE mytest (myval char (1));
INSERT INTO mytest VALUES ('a'),('b'),('c'),('s'),('t');
DELETE FROM mytest WHERE 't';
SELECT * FROM mytest;
 myval
---
(0 rows)

Geoff

Re: log_min_duration_statement

[bhargav kamineni]

> Hi Team,,
>
> I have set log_min_duration_statement=1000 as my configuration but when
> i observe log file i could see queries that ran below 1000ms are also
> getting logged , for instance
> duration: 0.089 ms
>   duration: 0.175 ms
>   duration: 0.139 ms
>   duration: 0.451 ms
> duration: 0.136 ms
>   duration: 0.340 ms
>   duration: 0.150 ms
> duration: 0.440 ms
> duration: 0.338 ms
>   duration: 0.156 ms
> tion: 0.396 ms
>   duration: 0.221 ms
> duration: 0.161 ms
>   *

>The above would not happen to be autovacuum statements?

Nope, those are not autovaccum related logs , they are related to queries
only.

You have log_autovacuum_min_duration = 0 below.

On Thu, 24 Jan 2019 at 20:53, Adrian Klaver 
wrote:

> On 1/24/19 6:23 AM, bhargav kamineni wrote:
> > Hi Team,,
> >
> > I have set log_min_duration_statement=1000 as my configuration but when
> > i observe log file i could see queries that ran below 1000ms are also
> > getting logged , for instance
> > duration: 0.089 ms
> >   duration: 0.175 ms
> >   duration: 0.139 ms
> >   duration: 0.451 ms
> > duration: 0.136 ms
> >   duration: 0.340 ms
> >   duration: 0.150 ms
> > duration: 0.440 ms
> > duration: 0.338 ms
> >   duration: 0.156 ms
> > tion: 0.396 ms
> >   duration: 0.221 ms
> > duration: 0.161 ms
> >   *
>
> The above would not happen to be autovacuum statements?
>
> You have log_autovacuum_min_duration = 0 below.
>
> > these are my logsettings
> >
> >name   |  setting
> >
> -+---
> >   log_autovacuum_min_duration | 0
> >   log_checkpoints | on
> >   log_connections | on
> >   log_destination | syslog
> >   log_directory   | log
> >   log_disconnections  | on
> >   log_duration| off
> >   log_error_verbosity | default
> >   log_executor_stats  | off
> >   log_file_mode   | 0600
> >   log_filename| postgresql-%Y-%m-%d_%H%M%S.log
> >   log_hostname| off
> >   log_line_prefix |
> > appname=%a,user=%u,db=%d,pid=%p,txid=%x,vtxid=%v
> >   log_lock_waits  | on
> >   log_min_duration_statement  | 1000
> >   log_min_error_statement | error
> >   log_min_messages| error
> >   log_parser_stats| off
> >   log_planner_stats   | off
> >   log_replication_commands| off
> >   log_rotation_age| 1440
> >   log_rotation_size   | 10240
> >   log_statement   | all
> >   log_statement_stats | off
> >   log_temp_files  | 0
> >   log_timezone| Navajo
> >   log_truncate_on_rotation| off
> >   logging_collector   | on
> >   max_logical_replication_workers | 4
> >   syslog_facility | local4
> >   syslog_ident| postgres
> >   syslog_sequence_numbers | on
> >   syslog_split_messages   | on
> >   wal_log_hints   | off
> >
> > Regards,
> > Bhargav K
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Casting Integer to Boolean in assignment

[Adrian Klaver]

On 1/24/19 7:48 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 15:40, Adrian Klaver  wrote:

delete from delete_test where

and then forget the 'field =' part. Though my more common mistake along
that line is:

delete from delete_test;

At any rate, if it can be done it will be done.


If you follow that logic, then having a single boolean test at all
should be invalid.

CREATE TABLE mytest (myval char (1));
INSERT INTO mytest VALUES ('a'),('b'),('c'),('s'),('t');
DELETE FROM mytest WHERE 't';
SELECT * FROM mytest;
  myval
---
(0 rows)


People are going to make mistakes that is a given. Eliminating a boolean 
test is not going to change that. Where this particular sub-thread 
started was with this from a previous post of yours:


"My own opinion is that non-0 should implicitly cast as true and 0
should cast as false. ..."

That opens an infinite number of values that could be seen as True. That 
in turn leads to greater chance of fat-thumbing yourself into an oops. 
Like you say it is a matter of opinion. The projects opinion is here:


https://www.postgresql.org/docs/11/datatype-boolean.html

and it works for me.



Geoff




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Casting Integer to Boolean in assignment

[Geoff Winkless]

On Thu, 24 Jan 2019 at 16:00, Adrian Klaver  wrote:
> People are going to make mistakes that is a given. Eliminating a boolean
> test is not going to change that.

I still think that if you've got to the point where you're actually
part-way through writing a clause you're unlikely to forget to
complete it.

Missing out a clause altogether is understandable but writing half of
one? Even if you weren't sure what the value was you would probably
write

WHERE myfield=

and then have to go and look it up.

> Like you say it is a matter of opinion. The projects opinion is here:
>
> https://www.postgresql.org/docs/11/datatype-boolean.html
>
> and it works for me.

And you're welcome to it. I'm not arguing for it changing. I'm simply
stating that I'm very pleased that the default behaviour can be
changed, because in my opinion writing a bunch of explicit casts in a
query is a surefire path to unreadable code.

Geoff

Re: Casting Integer to Boolean in assignment

[Alexandre GRAIL]

On 24/01/2019 12:45, Geoff Winkless wrote:

My own opinion is that non-0 should implicitly cast as true and 0
should cast as false. I just run

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
  SELECT c.oid
  FROM pg_cast c
  inner join pg_type src ON src.oid = c.castsource
  inner join pg_type tgt ON tgt.oid = c.casttarget
  WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
  OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%')
  OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%')
  OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%')
  OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int')
);


Thanks Geoff for this solution, I was thinking it cannot be changed ! I 
end up doing this :


UPDATE pg_cast SET castcontext = 'a' WHERE oid IN (
 SELECT c.oid
 FROM pg_cast c
 inner join pg_type src ON src.oid = c.castsource
 inner join pg_type tgt ON tgt.oid = c.casttarget
 WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
);

Only to have 0/1 => bool working in assignment. It saved me from 
injecting ALTER TYPE before and after any INSERT/UPDATE.


In my case I don't control the query which is auto generated. (And the 
framework assumes 1 and 0 are safe boolean values without cast or quote. 
Changing that is not possible.)

Re: Casting Integer to Boolean in assignment

[Alexandre GRAIL]

On 24/01/2019 17:19, Geoff Winkless wrote:

Like you say it is a matter of opinion. The projects opinion is here:

https://www.postgresql.org/docs/11/datatype-boolean.html

and it works for me.

And you're welcome to it. I'm not arguing for it changing. I'm simply
stating that I'm very pleased that the default behaviour can be
changed, because in my opinion writing a bunch of explicit casts in a
query is a surefire path to unreadable code.


And it may be just a matter of opinion but for me 0 and 1 "naked", *do* 
represent boolean value. More than 't' or 'f', if you consider all the 
computer history...


In the end it would be very nice to add a pointer in the documentation 
about this behavior and a quick workaround for the ones who really need 
it. That would be great :D !


Thanks all !







--
Alexandre GRAIL
/Ingénieur Logiciel // Chef de Projet/
*Tél.* +33 6 27 40 77 44
Augure Engineering 

Recommended Hardware requirements for PostgreSQL DB Server.

[Kaushal Shriyan]

Hi,

Are there any recommended hardware requirements to setup PostgreSQL DB
server?  I checked https://wiki.postgresql.org/wiki/Database_Hardware but
unable to find it. I am going to install Postgresql 9.6 version in AWS (
https://aws.amazon.com/ec2/instance-types/). I will appreciate if someone
can suggest how many CPU cores, physical memory and disk space is required
for the setup.

Thanks in Advance and i look forward to hearing from you.

Best Regards,

Kaushal

Re: Recommended Hardware requirements for PostgreSQL DB Server.

[Ron]

On 1/24/19 12:06 PM, Kaushal Shriyan wrote:

Hi,

Are there any recommended hardware requirements to setup PostgreSQL DB 
server?  I checked https://wiki.postgresql.org/wiki/Database_Hardware but 
unable to find it. I am going to install Postgresql 9.6 version in AWS 
(https://aws.amazon.com/ec2/instance-types/). I will appreciate if someone 
can suggest how many CPU cores, physical memory and disk space is required 
for the setup.


Thanks in Advance and i look forward to hearing from you.


That's impossible to answer unless you say something about:

 * the size of the database,
 * what use type (OLTP, OLAP, DSS, mixed usage, etc),
 * number of concurrent users,
 * etc

http://www.catb.org/esr/faqs/smart-questions.html


--
Angular momentum makes the world go 'round.

duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Hello.

I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this 
issue...

I've found a similar thread, but going from 8.4 to 9.2:
https://www.postgresql.org/message-id/flat/52C44FA9.2080500%40gmail.com#14ab24f6c94287dd4c435652cb2d77f8

This ended up without really pinpointing the problem as the poster did not 
follow up.
Hopefully I'll have more luck...

I'm posting below more output, here I'll crop the interesting bits.
(don't know if this is pertinent for this discussion but Pg_upgrade complains 
with 2 errors but still executes, and -check turns out ok.
These are:
pg_upgrade: could not start process for command ""D:\Program Files\PostgreSQL\9.
5\bin\pg_upgrade"  -U postgres --jobs=6 -d "D:\Program Files\PostgreSQL\9.3\data
" -b "D:\Program Files\PostgreSQL\9.3\bin" -D "D:\Program Files\PostgreSQL\9.5\d
ata" -B "D:\Program Files\PostgreSQL\9.5\bin"": error code 193
pg_upgrade: could not re-execute with restricted token: error code 3
)

What I'm seeing from pg_upgrade:

Restoring database schemas in the new cluster
  template1
*failure*

Consult the last few lines of "pg_upgrade_dump_16393.log" for
the probable cause of the failure.
Failure, exiting

So, looking at the log file, it's not very long, at the end where it blows up:
pg_restore: creating TYPE "sde.se_coord"
pg_restore: creating SHELL TYPE "sde.se_extent"
pg_restore: creating FUNCTION "sde.st_extent_in("cstring")"
pg_restore: creating FUNCTION "sde.st_extent_out("sde"."se_extent")"
pg_restore: creating FUNCTION "sde.st_extent_recv("internal")"
pg_restore: creating FUNCTION "sde.st_extent_send("sde"."se_extent")"
pg_restore: creating TYPE "sde.se_extent"
pg_restore: creating FUNCTION "sde.st_envelope_in("cstring")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4712; 1255 1141726 FUNCTION 
st_envelope_in("cstring") sde
pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key 
value violates unique constraint "pg_type_oid_index"
DETAIL:  Key (oid)=(1142573) already exists.
Command was: CREATE FUNCTION "sde"."st_envelope_in"("cstring") RETURNS 
"sde"."st_envelope"
LANGUAGE "c" IMMUTABLE STRICT
AS 'st_g...
command: "D:\Program Files\PostgreSQL\9.5\bin/pg_restore" --port 50432 
--username ^"postgres^" --exit-on-error --verbose --dbname ^"dbname^=postgis^" 
"pg_upgrade_dump_16393.custom" >> "pg_upgrade_dump_16393.log" 2>&1

It says it's trying to create function sde.st_envelope_in a 2nd time.

It seems to me that while creating TYPE sde.se_coord (or is it SHELL TYPE 
sde.se_extent?) this function was created.
Right afterwards, when creating TYPE sde.se_extent, the same function is also 
being created, causing the duplicate error...

At least that's my limited understanding...

Can anyone please take a look? I can send any file from the pg_upgrade process.

Many thanks,
Duarte


--full 
outputs-
Pg_upgrade output:
...
C:\work\upgrade_postgresql\testes>"D:\Program Files\PostgreSQL\9.5\bin\pg_upgrad
e" -U postgres --jobs=6 -d "D:\Program Files\PostgreSQL\9.3\data" -b "D:\Program
Files\PostgreSQL\9.3\bin" -D "D:\Program Files\PostgreSQL\9.5\data" -B "D:\Prog
ram Files\PostgreSQL\9.5\bin"
pg_upgrade: could not start process for command ""D:\Program Files\PostgreSQL\9.
5\bin\pg_upgrade"  -U postgres --jobs=6 -d "D:\Program Files\PostgreSQL\9.3\data
" -b "D:\Program Files\PostgreSQL\9.3\bin" -D "D:\Program Files\PostgreSQL\9.5\d
ata" -B "D:\Program Files\PostgreSQL\9.5\bin"": error code 193
pg_upgrade: could not re-execute with restricted token: error code 3
Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for invalid "line" user columnsok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user  ok
Checking for prepared transactions  ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows on the new clusterok
Deleting files from new pg_clog ok
Copying old pg_clog to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multix

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Tom Lane]

Duarte Carreira  writes:
> I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this 
> issue...

9.5.what?

Perusing the commit logs, I note that 9.5.3 included a fix for a
pg_upgrade issue that could possibly lead to this symptom, see
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=196870f2f

If you are on 9.5.recent, it'd be worth looking closer, because this
is certainly pretty odd.  One wouldn't expect a CREATE FUNCTION to
result in assignment of a type OID, at least not in pg_dump/pg_upgrade
scripts --- they should always put out a shell CREATE TYPE first.

By any chance, if you attempt a "pg_dump -s" from the problematic database,
does it emit any warnings (about dependency loops, perhaps)?

regards, tom lane

Re: log_min_duration_statement

[Adrian Klaver]

On 1/24/19 7:52 AM, bhargav kamineni wrote:

Hi Team,,





 >The above would not happen to be autovacuum statements?
Nope, those are not autovaccum related logs , they are related to 
queries only.


On the chance that this setting is being overridden somewhere what does 
the below show when run as a superuser?:


select * from pg_settings where name= 'log_min_duration_statement';


More info:

https://www.postgresql.org/docs/11/view-pg-settings.html

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Hi Tom.

It's 9.5 latest.

I'll try pgdump tomorrow.

Obter o Outlook para Android


From: Tom Lane 
Sent: Thursday, January 24, 2019 9:02:25 PM
To: Duarte Carreira
Cc: pgsql-gene...@postgresql.org
Subject: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira  writes:
> I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this 
> issue...

9.5.what?

Perusing the commit logs, I note that 9.5.3 included a fix for a
pg_upgrade issue that could possibly lead to this symptom, see
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=196870f2f

If you are on 9.5.recent, it'd be worth looking closer, because this
is certainly pretty odd.  One wouldn't expect a CREATE FUNCTION to
result in assignment of a type OID, at least not in pg_dump/pg_upgrade
scripts --- they should always put out a shell CREATE TYPE first.

By any chance, if you attempt a "pg_dump -s" from the problematic database,
does it emit any warnings (about dependency loops, perhaps)?

regards, tom lane

Geographical multi-master replication

[Ruben Rubio Rey]

Hi Everybody,

I am currently working with a client that has requirements for
geographically dispersed multi-master replication.

The current solution that we have in place is the BDR 1.0.7, which is
asynchronous and eventually consistent and it is actually very close to
real time. So far we are managing a small cluster of three nodes and we are
very happy with this solution.

The problem is that the version for BDR 1.0.7, which has an implementation
for postgres 9.4, will be on end of live at the end of this year.
Unfortunately the paid solution is out of our budget, so we currently have
two options: find an alternative or remove the multi-region implementation.
We are currently looking for alternatives.

Reading the different replication options in PostgreSQL 11
,
it looks like there are two options that might be suitable:

1 - *Logical Replication*, which according to the documentation *Logical
replication doesn't require a particular server to be designated as a
master or a replica but allows data to flow in multiple directions. *It
should be suitable. The limitations

are OK for us, however there is no such thing as conflict resolution
. If
there is a conflict, the replication just stops. In our application
(currently replicated using BDR), we have a very small amount of conflicts,
and when conflict happens they are harmless.

What do you think about this solution for geographically dispersed
multi-master replication ?
Is there any configuration options for the conflict resolution in the
Logical Replication ?

2 - *Asynchronous Multimaster Replication* - For example by using Bucardo.
Does anybody has experience for using Bucardo for Asynchronous Multimaster
Replication ? Would be suitable for geographically dispersed
infrastructures ?

Is there any other option available that I might be missing ?

Thanks in advance,
Ruben

Re: Geographical multi-master replication

[Jeremy Finzel]

>
> The problem is that the version for BDR 1.0.7, which has an implementation
> for postgres 9.4, will be on end of live at the end of this year.
> Unfortunately the paid solution is out of our budget, so we currently have
> two options: find an alternative or remove the multi-region implementation.
> We are currently looking for alternatives.
>

You are missing all of the alternatives here.  Why don't you consider
upgrading from postgres 9.4 and with it to a supported version of BDR?
There is nothing better you can do to keep your infrastructure up to date,
performant, secure, and actually meet your multi-master needs than to
upgrade to a newer version of postgres which does have BDR support.

Even "stock" postgres 9.4 is set for end of life soon.  Upgrade!

Thanks,
Jeremy

Re: [pgbackrest] Expiring the last backup?

[David Steele]

On 1/24/19 5:07 PM, Ron wrote:


Are these the steps, or am I missing something?

$ pgbackrest stop
$ pgbackrest stanza-delete --stanza=mystanza--force


That looks right but no need for --force.  That's what the `stop` is for 
-- to let pgBackRest know you really mean to do this.


See documentation for more information:
https://pgbackrest.org/user-guide.html#delete-stanza

--
-David
da...@pgmasters.net

Re: Casting Integer to Boolean in assignment

[Laurenz Albe]

Alexandre GRAIL wrote:
> On 24/01/2019 12:45, Geoff Winkless wrote:
> > My own opinion is that non-0 should implicitly cast as true and 0
> > should cast as false. I just run
> > 
> > UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
> >   SELECT c.oid
> >   FROM pg_cast c
> >   inner join pg_type src ON src.oid = c.castsource
> >   inner join pg_type tgt ON tgt.oid = c.casttarget
> >   WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
> >   OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%')
> >   OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%')
> >   OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%')
> >   OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int')
> > );
> 
> Thanks Geoff for this solution, I was thinking it cannot be changed ! I 
> end up doing this :
> 
> UPDATE pg_cast SET castcontext = 'a' WHERE oid IN (
>   SELECT c.oid
>   FROM pg_cast c
>   inner join pg_type src ON src.oid = c.castsource
>   inner join pg_type tgt ON tgt.oid = c.casttarget
>   WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
> );
> 
> Only to have 0/1 => bool working in assignment. It saved me from 
> injecting ALTER TYPE before and after any INSERT/UPDATE.
> 
> In my case I don't control the query which is auto generated. (And the 
> framework assumes 1 and 0 are safe boolean values without cast or quote. 
> Changing that is not possible.)

You are aware that catalog modifications are not supported, right?

One of the reasons is that these modifications will be gone after a
major upgrade, and you'll have to remember to re-apply them.

Making type casts more liberal increases the risk of ambiguities
during type resolution, which cause error messages if PostgreSQL cannot
find a single best candidate.
So there is a price you are paying - but if it works for you, you
probably won't mind.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Geographical multi-master replication

[Andreas Kretschmer]

Am 25.01.19 um 06:10 schrieb Jeremy Finzel:


The problem is that the version for BDR 1.0.7, which has an
implementation for postgres 9.4, will be on end of live at the end
of this year. Unfortunately the paid solution is out of our
budget, so we currently have two options: find an alternative or
remove the multi-region implementation. We are currently looking
for alternatives.


You are missing all of the alternatives here.  Why don't you consider 
upgrading from postgres 9.4 and with it to a supported version of 
BDR?  There is nothing better you can do to keep your infrastructure 
up to date, performant, secure, and actually meet your multi-master 
needs than to upgrade to a newer version of postgres which does have 
BDR support.


Even "stock" postgres 9.4 is set for end of life soon. Upgrade!


ACK!

Sure, you have to pay for a support contract, and this isn't for free, 
but you will get a first-class support for BDR. If you really needs a 
worldwide distributed multi-master solution you should be able to buy that.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: [pgbackrest] Expiring the last backup?

[Ron]

On 1/24/19 11:22 PM, David Steele wrote:

On 1/24/19 5:07 PM, Ron wrote:


Are these the steps, or am I missing something?

$ pgbackrest stop
$ pgbackrest stanza-delete --stanza=mystanza--force


That looks right but no need for --force.  That's what the `stop` is for 
-- to let pgBackRest know you really mean to do this.


See documentation for more information:
https://pgbackrest.org/user-guide.html#delete-stanza


Turns out that --force was required.  stanza-delete failed, complained that 
the postmaster was running, and told me to use "--force" if I really wanted 
to drop it.



--
Angular momentum makes the world go 'round.

Re: [External] Re: Geographical multi-master replication

[Vijaykumar Jain]

I do not know the use case but we did try the following.
We had a small requirement wrt some regional data written to tables but
needs to be available to all regions.
We made use of logical replication to replicate/publish  each local table
to all the other regions ( like a many to many)
In theory, if there were no network delay all regions will have all the
data at any point of time.
If there is a wan outage b/w A and B,
They both still talk to C and c can be considered the region with more
correct data, till we have others recovered.

basically given we had disjoint data, we were able to make use of this
feature.

And it it definitely not for HA, there are other options for it.

On Fri, 25 Jan 2019 at 11:16 AM Andreas Kretschmer 
wrote:

>
>
> Am 25.01.19 um 06:10 schrieb Jeremy Finzel:
> >
> > The problem is that the version for BDR 1.0.7, which has an
> > implementation for postgres 9.4, will be on end of live at the end
> > of this year. Unfortunately the paid solution is out of our
> > budget, so we currently have two options: find an alternative or
> > remove the multi-region implementation. We are currently looking
> > for alternatives.
> >
> >
> > You are missing all of the alternatives here.  Why don't you consider
> > upgrading from postgres 9.4 and with it to a supported version of
> > BDR?  There is nothing better you can do to keep your infrastructure
> > up to date, performant, secure, and actually meet your multi-master
> > needs than to upgrade to a newer version of postgres which does have
> > BDR support.
> >
> > Even "stock" postgres 9.4 is set for end of life soon. Upgrade!
>
> ACK!
>
> Sure, you have to pay for a support contract, and this isn't for free,
> but you will get a first-class support for BDR. If you really needs a
> worldwide distributed multi-master solution you should be able to buy that.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
> --

Regards,
Vijay

Re: [pgbackrest] Expiring the last backup?

[David Steele]

On 1/25/19 8:02 AM, Ron wrote:

On 1/24/19 11:22 PM, David Steele wrote:

On 1/24/19 5:07 PM, Ron wrote:


Are these the steps, or am I missing something?

$ pgbackrest stop
$ pgbackrest stanza-delete --stanza=mystanza--force


That looks right but no need for --force.  That's what the `stop` is 
for -- to let pgBackRest know you really mean to do this.


See documentation for more information:
https://pgbackrest.org/user-guide.html#delete-stanza


Turns out that --force was required.  stanza-delete failed, complained 
that the postmaster was running, and told me to use "--force" if I 
really wanted to drop it.


Whoops -- I guess I should have read the docs, too.  It's very unusual 
to be deleting a stanza for a running cluster and we made it difficult 
on purpose.


--
-David
da...@pgmasters.net