ODBC Driver for 9.4

[Mohammed Siddiqui]

what the latest version of their ODBC Driver that supports the 9.4 PG DB ?

Regards,
Mohammed Younus Siddiqui

Re: Default Privilege Table ANY ROLE

[Nicolas Paris]

On Fri, Nov 16, 2018 at 03:17:59PM -0500, Tom Lane wrote:
> Stephen Frost  writes:
> > There was much discussion of being able to have 'FOR ALL ROLES' or
> > similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a
> > lot of concern about one user getting to define the default privileges
> > for objects created by some other user.
> 
> Yeah, it's hard to see how you could allow such a command to anybody
> but a superuser.
> 

I have some applications using specific schema. I don't wan't them to be
superuser, but I wan't them to be able to access any table in that
schema.

Because many users are able to create tables in that schema, I have to
write one ALTER DEFAULT PRIVILEGE foreach user.


Any chance to have superuser per schema ?


-- 
nicolas

Re: Missing RHEL rpm(pg_catcheck-11) in postgres 11 repo.

[Devrim Gündüz]

Hi,

On Fri, 2018-11-09 at 09:53 +, Chatterjee, Satyajit wrote:

> We are looking to upgrade our application from Postgres 9.5 to 11x, but we
> are unable to find the RPM named 'pg_catcheck-11x' for Redhat:  
> https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.5-x86_64/.
> 
> We request your help in getting this rpm to the repo.

Pushed packages yesterday. Sorry for the inconvenience.

Regards,

-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: Syndicating PostgreSQL mailing list to Discourse

[legrand legrand]

Hello,

What you are proposing with https://rubytalk.org/ seems very interesting.

It offers a quick view on mobile of "latests posts for all sites" in one
click, 
and many other grouping /filtering options (that miss PostgreSQL website),
for users that don't use fat client mailling list system (like me).

This seems even better than nabble www.postgresql-archive.org, that is 
mobile friendly, and even *even* better as you don't include Ads.

As you can see Pg community members are very frightened by this option that 
would permit "seamlessly interact with the mailing list" (there are many
demands
to ask nabble to remove it,
https://www.postgresql-archive.org/postgresql-archive-org-td6035059i20.html#a6059185).
 

Maybe you will have a better answer if you propose a pure Read-Only mailling
list system 
- without any possibility to reply from your site,
- promising NO Ads for ever (and explaining how you get the money for
running costs),
- ...

Are there any other mobile users here, to vote for this solution (maybe
adding other restrictions) ?

Regards
PAscal



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

Re: Syndicating PostgreSQL mailing list to Discourse

[Josef Šimánek]

Is there source-code available for rubytalk.org mail-list sync part?

st 21. 11. 2018 v 15:53 odesílatel legrand legrand <
legrand_legr...@hotmail.com> napsal:

> Hello,
>
> What you are proposing with https://rubytalk.org/ seems very interesting.
>
> It offers a quick view on mobile of "latests posts for all sites" in one
> click,
> and many other grouping /filtering options (that miss PostgreSQL website),
> for users that don't use fat client mailling list system (like me).
>
> This seems even better than nabble www.postgresql-archive.org, that is
> mobile friendly, and even *even* better as you don't include Ads.
>
> As you can see Pg community members are very frightened by this option
> that
> would permit "seamlessly interact with the mailing list" (there are many
> demands
> to ask nabble to remove it,
>
> https://www.postgresql-archive.org/postgresql-archive-org-td6035059i20.html#a6059185).
>
>
> Maybe you will have a better answer if you propose a pure Read-Only
> mailling
> list system
> - without any possibility to reply from your site,
> - promising NO Ads for ever (and explaining how you get the money for
> running costs),
> - ...
>
> Are there any other mobile users here, to vote for this solution (maybe
> adding other restrictions) ?
>
> Regards
> PAscal
>
>
>
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>

Re: Syndicating PostgreSQL mailing list to Discourse

[Adrian Klaver]

On 11/21/18 6:53 AM, legrand legrand wrote:

Hello,

What you are proposing with https://rubytalk.org/ seems very interesting.

It offers a quick view on mobile of "latests posts for all sites" in one
click,
and many other grouping /filtering options (that miss PostgreSQL website),
for users that don't use fat client mailling list system (like me).

This seems even better than nabble www.postgresql-archive.org, that is
mobile friendly, and even *even* better as you don't include Ads.

As you can see Pg community members are very frightened by this option that
would permit "seamlessly interact with the mailing list" (there are many
demands


That is overstating it. The concerns are based on past experiences and 
the mess they made of the mailing list.



to ask nabble to remove it,
https://www.postgresql-archive.org/postgresql-archive-org-td6035059i20.html#a6059185).

Maybe you will have a better answer if you propose a pure Read-Only mailling
list system
- without any possibility to reply from your site,
- promising NO Ads for ever (and explaining how you get the money for
running costs),
- ...

Are there any other mobile users here, to vote for this solution (maybe
adding other restrictions) ?

Regards
PAscal



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





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

Re: ODBC Driver for 9.4

[Adrian Klaver]

On 11/20/18 10:16 PM, Mohammed Siddiqui wrote:

what the latest version of their ODBC Driver that supports the 9.4 PG DB ?


https://www.postgresql.org/ftp/odbc/versions/

"Unless you have have a specific reason not to do so, you should always 
download the latest version."


So 11.00..

If you want more specific information I would suggest the --odbc list:

https://www.postgresql.org/list/pgsql-odbc/



Regards,
Mohammed Younus Siddiqui



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

Sv: Re: Syndicating PostgreSQL mailing list to Discourse

[Andreas Joseph Krogh]

På onsdag 21. november 2018 kl. 15:53:34, skrev legrand legrand <
legrand_legr...@hotmail.com >:
Hello,

 What you are proposing with https://rubytalk.org/ seems very interesting.

 It offers a quick view on mobile of "latests posts for all sites" in one
 click,
 and many other grouping /filtering options (that miss PostgreSQL website),
 for users that don't use fat client mailling list system (like me).

 This seems even better than nabble www.postgresql-archive.org, that is
 mobile friendly, and even *even* better as you don't include Ads.

 As you can see Pg community members are very frightened by this option that
 would permit "seamlessly interact with the mailing list" (there are many
 demands
 to ask nabble to remove it,
 
https://www.postgresql-archive.org/postgresql-archive-org-td6035059i20.html#a6059185).

 Maybe you will have a better answer if you propose a pure Read-Only mailling
 list system
 - without any possibility to reply from your site,
 - promising NO Ads for ever (and explaining how you get the money for
 running costs),
 - ...

 Are there any other mobile users here, to vote for this solution (maybe
 adding other restrictions) ?

 Regards
 PAscal
 
 
Scala (scala-lang.org) moved from mailing-list to Discourse a while ago and 
it's in my oppinion a disaster. No matter what they tell you, it does _not_ 
work well with email-only. Replying, quoting and reading history is a mess imo.
 
 
--
 Andreas Joseph Krogh

RE: pg_dump out of memory for large table with LOB

[Jean-Marc Lessard]

JMLessard wrote:
> What about updates where the bytea do not changed. Does a new copy of the 
> bytea will be made in the toast table or new row will point to the original 
> bytea?
> > https://www.postgresql.org/docs/current/storage-toast.html says:
> > The TOAST management code is triggered only when a row value to be stored 
> > in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The 
> > TOAST code will compress
> > and/or move field values out-of-line until the row value is shorter than 
> > TOAST_TUPLE_TARGET bytes (also normally 2 kB, adjustable) or no more gains 
> > can be had. During an UPDATE
> > operation, values of unchanged fields are normally preserved as-is; so an 
> > UPDATE of a row with out-of-line values incurs no TOAST costs if none of 
> > the out-of-line values change.
> Does it means, no incurs cost to generate the out of line toast, but that a 
> copy of the bytea is still made for the new line?

I bench mark it as follow:
UPDATE table SET mod_tim=mod_tim;
The relpages of the table doubled, but the relpages of the toast table did not 
changed.

Jean-Marc Lessard
Administrateur de base de donn?es / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Parallel query and number of connections.

[Alessandro Aste]

Hi there, we are running postgresql 10.5 on a centos 7 server.

We're seeing multiple connections (in pg_stat_activity) from our
application with the same query , same user, same application_name, same
query_start etc.

We are 100% sure the query is duplicated and not referring to multiple
queries issued.

The only difference is obviosly the pid and client_addr, that is populated
in any of the connections but the first one.

I was wondering if this can be related to the parallel queries of
postgresql. Is it supposed to spawn multiple "connections" ?

Thank you in advance,

Alessandro.

RE: pg_dump out of memory for large table with LOB

[Jean-Marc Lessard]

Thanks Tom Lane for your answer
Same issue for pg_dump and LOB was also reported in 
https://postgrespro.com/list/thread-id/2211186

Concerning the 25% waste of space we experienced with LOB:
We are using LOB because we save jpeg2000 images into the DB.
We display them as thumbnails in a 6x10 multi-viewer (60 images displayed at a 
time) that the user can scroll to next ones.
When retrieving images into the multi-viewer, we only read the first 30% of the 
jpeg2000.
Even if jpeg2000 images are relatively small (about 20MB), retrieving thousands 
of images partially greatly speedup the display.

src/include/storage/large_object.h:
/*
 * Each "page" (tuple) of a large object can hold this much data
 *
 * We could set this as high as BLCKSZ less some overhead, but it seems
 * better to make it a smaller value, so that not as much space is used
 * up when a page-tuple is updated.  Note that the value is deliberately
 * chosen large enough to trigger the tuple toaster, so that we will
 * attempt to compress page tuples in-line.  (But they won't be moved off
 * unless the user creates a toast-table for pg_largeobject...)
 *
 * Also, it seems to be a smart move to make the page size be a power of 2,
 * since clients will often be written to send data in power-of-2 blocks.
 * This avoids unnecessary tuple updates caused by partial-page writes.
 *
 * NB: Changing LOBLKSIZE requires an initdb.
 */
#define LOBLKSIZE   (BLCKSZ / 4)

Here is my understanding, please correct me if I am wrong.
If the data is compressible, each 2k (2048) uncompressed pieces should be 
compressed to pieces smaller than 2000 bytes, so that 4 rows and more can fit 
in 8k data block.
In our case as jpeg2000 images are already compressed, the 2K pieces remain 
2048 bytes after LO compression and only 3 pieces can fit in a block.
Can we change the LOBLKSIZE to 2000 bytes to fit 4 rows in a block as follow?
#define LOBLKSIZE   2000
Must the LOBLKSIZE be a power-of-2?
Is there any optimization expecting a power-of-2 value?

Thank you.

Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Re: replication lag despite corrective config

[Rene Romero Benavides]

You're welcome.
Since last Saturday when you addressed the 10 hour delay, with the new
settings, have you seen more of such delay incidents? what the previous
settings were?
Beware that hot_standby_feedback = on and such long queries in the replica
can increase bloat in the master, are you measuring bloat? if so, do you
notice a significant increase?

Am Di., 20. Nov. 2018 um 23:08 Uhr schrieb Wyatt Alt :

> Hi Rene,
> On 11/19/18 8:46 PM, Rene Romero Benavides wrote:
>
> Not sure about the root cause but I can make these observations and raise
> some questions:
> 1) 9.6.6 is five bug fix versions behind
>
> Valid point to raise.
>
> 2) 300GB is so big a table, wouldn't make sense to you to partition it ?
> 2a) or if it's partitioned, doesn't the time of creation or dropping of
> new partitions match the time of the conflict?
>
> Partitioning is in the works, but none at the moment.
>
>
> 3) can you track long running transactions on the master?
> 4) what are the isolation levels on master / replica?
>
> Transaction times on the master max out around two minutes. On the replica
> they are much longer -- numerous 1 - 2 hour transactions per day, and
> occasional ones as long as 10 - 20 hours. Isolation levels are read
> committed everywhere.
>
> 5) check for active locks in the replica, I guess you should see some
> blocked transactions during big delays, I've seen this in the past when
> standby_feedback is turned off.
> 6) any out of the ordinary messages in the replica's logs? any evidence
> that has been canceling statements?
>
> I'll make a note to record the active locks next time. I haven't seen
> anything unusual in the logs during these incidents, but have observed
> statements getting canceled at other times, which is why I think the config
> mostly works.
>
> 7) are master and replica exactly the same in terms of resources and main
> parameters?
> 8) how is performance in both nodes while the big delay is happening? IO /
> cpu load / etc.
>
> This brings up a good detail I forgot to mention originally. During the
> last incident, IO utilization on the replica was near 100%, and had been
> for several hours, which I believe was due to the long queries I canceled.
> Now that I think about it, I wonder if the lag may have arisen from IO
> contention between the query and WAL replay, rather than a query conflict.
>
>
> Also, check this out:
> https://www.alibabacloud.com/forum/read-383
>
> Thanks, interesting reading.
>
>
> Am Mo., 19. Nov. 2018 um 21:46 Uhr schrieb Wyatt Alt  >:
>
>> Sorry, I see now there was a similar question a few days ago:
>>
>> https://www.postgresql.org/message-id/CAJw4d1WtzOdYzd8Nq2=ufk+z0jy0l_pfg9tvcwprmt3nczq...@mail.gmail.com
>>
>> Two ideas proposed (aside from disconnects):
>> * Autovacuum is truncating a page on the master and taking an
>> AccessExclusiveLock on the table in use on the replica
>> * A "pin conflict", which I'm still unfamiliar with.
>>
>> The user's response says they are in the first bucket, but the argument
>> relies on max_standby_streaming_delay set to -1, while mine is 5 minutes. I
>> need to understand pin conflicts better, but the likely scenario Andrew
>> outlined doesn't apply to me. My offending queries were doing bitmap heap
>> scans on a 300GB table.
>>
>> Reading the thread I see Andres ask for the "precise conflict" the user
>> gets -- is there a way I can get that without a datadir? And to re-frame
>> the original question, are there causes of replication lag that
>> max_standby_streaming_delay would not be expected to prevent, that would be
>> resolved by killing long standby queries? If so, what's the best way to
>> confirm?
>>
>> Wyatt
>>
>> On Mon, Nov 19, 2018 at 5:46 PM Wyatt Alt  wrote:
>>
>>> I've been struggling to eliminate replication lag on a Postgres 9.6.6
>>> instance on Amazon RDS. I believe the lag is caused by early cleanup
>>> conflicts from vacuums on the master, because I can reliably resolve it by
>>> killing long-running queries on the standby. I most recently saw ten hours
>>> of lag on Saturday and addressed it this way.
>>>
>>> The standby is running with
>>> hot_standby_feedback = on
>>> max_standby_streaming_delay = 5min
>>> max_standby_archive_delay = 30s
>>>
>>> I am not using replication slots on the primary due to reported negative
>>> interactions with pg_repack on large tables.
>>>
>>> My rationale for the first two settings is that hot_standby_feedback
>>> should address my issues almost all the time, but that
>>> max_standby_streaming_delay would sometimes be necessary as a fallback, for
>>> instance in cases of a transient connection loss between the standby and
>>> primary. I believe these settings are mostly working, because lag is less
>>> frequent than it was when I configured them.
>>>
>>> My questions are,
>>> * Am I overlooking anything in my configuration?
>>> * What would explain lag caused by query conflicts given the
>>> max_standby_streaming_delay setting? Shouldn't those queries be getting

Re: replication lag despite corrective config

[Rene Romero Benavides]

How big have been the delays after the new settings? I guess significantly
lower than before, right? how much have they decreased?

Am Mi., 21. Nov. 2018 um 13:18 Uhr schrieb Rene Romero Benavides <
rene.romer...@gmail.com>:

> You're welcome.
> Since last Saturday when you addressed the 10 hour delay, with the new
> settings, have you seen more of such delay incidents? what the previous
> settings were?
> Beware that hot_standby_feedback = on and such long queries in the replica
> can increase bloat in the master, are you measuring bloat? if so, do you
> notice a significant increase?
>
> Am Di., 20. Nov. 2018 um 23:08 Uhr schrieb Wyatt Alt  >:
>
>> Hi Rene,
>> On 11/19/18 8:46 PM, Rene Romero Benavides wrote:
>>
>> Not sure about the root cause but I can make these observations and raise
>> some questions:
>> 1) 9.6.6 is five bug fix versions behind
>>
>> Valid point to raise.
>>
>> 2) 300GB is so big a table, wouldn't make sense to you to partition it ?
>> 2a) or if it's partitioned, doesn't the time of creation or dropping of
>> new partitions match the time of the conflict?
>>
>> Partitioning is in the works, but none at the moment.
>>
>>
>> 3) can you track long running transactions on the master?
>> 4) what are the isolation levels on master / replica?
>>
>> Transaction times on the master max out around two minutes. On the
>> replica they are much longer -- numerous 1 - 2 hour transactions per day,
>> and occasional ones as long as 10 - 20 hours. Isolation levels are read
>> committed everywhere.
>>
>> 5) check for active locks in the replica, I guess you should see some
>> blocked transactions during big delays, I've seen this in the past when
>> standby_feedback is turned off.
>> 6) any out of the ordinary messages in the replica's logs? any evidence
>> that has been canceling statements?
>>
>> I'll make a note to record the active locks next time. I haven't seen
>> anything unusual in the logs during these incidents, but have observed
>> statements getting canceled at other times, which is why I think the config
>> mostly works.
>>
>> 7) are master and replica exactly the same in terms of resources and main
>> parameters?
>> 8) how is performance in both nodes while the big delay is happening? IO
>> / cpu load / etc.
>>
>> This brings up a good detail I forgot to mention originally. During the
>> last incident, IO utilization on the replica was near 100%, and had been
>> for several hours, which I believe was due to the long queries I canceled.
>> Now that I think about it, I wonder if the lag may have arisen from IO
>> contention between the query and WAL replay, rather than a query conflict.
>>
>>
>> Also, check this out:
>> https://www.alibabacloud.com/forum/read-383
>>
>> Thanks, interesting reading.
>>
>>
>> Am Mo., 19. Nov. 2018 um 21:46 Uhr schrieb Wyatt Alt > >:
>>
>>> Sorry, I see now there was a similar question a few days ago:
>>>
>>> https://www.postgresql.org/message-id/CAJw4d1WtzOdYzd8Nq2=ufk+z0jy0l_pfg9tvcwprmt3nczq...@mail.gmail.com
>>>
>>> Two ideas proposed (aside from disconnects):
>>> * Autovacuum is truncating a page on the master and taking an
>>> AccessExclusiveLock on the table in use on the replica
>>> * A "pin conflict", which I'm still unfamiliar with.
>>>
>>> The user's response says they are in the first bucket, but the argument
>>> relies on max_standby_streaming_delay set to -1, while mine is 5 minutes. I
>>> need to understand pin conflicts better, but the likely scenario Andrew
>>> outlined doesn't apply to me. My offending queries were doing bitmap heap
>>> scans on a 300GB table.
>>>
>>> Reading the thread I see Andres ask for the "precise conflict" the user
>>> gets -- is there a way I can get that without a datadir? And to re-frame
>>> the original question, are there causes of replication lag that
>>> max_standby_streaming_delay would not be expected to prevent, that would be
>>> resolved by killing long standby queries? If so, what's the best way to
>>> confirm?
>>>
>>> Wyatt
>>>
>>> On Mon, Nov 19, 2018 at 5:46 PM Wyatt Alt  wrote:
>>>
 I've been struggling to eliminate replication lag on a Postgres 9.6.6
 instance on Amazon RDS. I believe the lag is caused by early cleanup
 conflicts from vacuums on the master, because I can reliably resolve it by
 killing long-running queries on the standby. I most recently saw ten hours
 of lag on Saturday and addressed it this way.

 The standby is running with
 hot_standby_feedback = on
 max_standby_streaming_delay = 5min
 max_standby_archive_delay = 30s

 I am not using replication slots on the primary due to reported
 negative interactions with pg_repack on large tables.

 My rationale for the first two settings is that hot_standby_feedback
 should address my issues almost all the time, but that
 max_standby_streaming_delay would sometimes be necessary as a fallback, for
 instance in cases of a transient connection loss between the standby and
>

Re: Parallel query and number of connections.

[Adrian Klaver]

On 11/21/18 9:45 AM, Alessandro Aste wrote:

Hi there, we are running postgresql 10.5 on a centos 7 server.

We're seeing multiple connections (in pg_stat_activity) from our 
application with the same query , same user, same application_name, same 
query_start etc.


We are 100% sure the query is duplicated and not referring to multiple 
queries issued.


The only difference is obviosly the pid and client_addr, that is 
populated in any of the connections but the first one.


client_addr being different would seem to indicate that this is 
something other then parallel query.




I was wondering if this can be related to the parallel queries of 
postgresql. Is it supposed to spawn multiple "connections" ?


Thank you in advance,

Alessandro.



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

Empty Range Bound Specified for Partition

[ramsiddu007]

Dear Professionals,
   Just i have installed "PostgreSQL 11.1". And i
have started to create partitions. I've created RANGE partition table as
like below.

pgsql> create table test_parent_partition(
item_id integer,
 item_name character varying,
 item_qty character varying)
 partition by range(item_qty);

pgsql>  create table test_upto_50 partition of test_parent_partition
  for values from ('0') to ('50');

Above 1st partition table was created. After that i have taken next step to
create another partition talbe. But while am creating another partition
table like below query.

pgsql>  create table test_upto_100 partition of test_parent_partition
 for values from ('51') to ('100');

   It was showing error like this..

ERROR: empty range bound specified for partition "test_upto_100" DETAIL:
Specified lower bound ('51') is greater than or equal to upper bound
('100'). SQL state: 42P17

I am not getting, please give me cause of this and solution.

-- 
*Thanks & Best Regards:*
Ramanna Gunde

*Don't complain about the HEAT,*

*PLANT A .*