Re: GIN Index for low cardinality

[Олег Самойлов]

Eh, I checked, you are right. Something terribly wrong with hash index in 
PostgreSQL. But there are another hash index gin(jsonb_path_ops), may be 
correctly say gin+hash index. Looked like it is the best for this purpose.

> 26 окт. 2018 г., в 19:27, Jeff Janes  написал(а):
> 
> On Thu, Oct 25, 2018 at 9:36 AM Олег Самойлов  > wrote:
> 
>> 17 окт. 2018 г., в 13:46, Ravi Krishna > > написал(а):
>> 
>> In  
>> https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/
>>  
>> 
>> 
>> it is mentioned:
>> 
>> "GIN, the most know non-default index type perhaps, has been actually around 
>> for ages (full-text search) and in short is perfect for indexing columns 
>> where there are lot of repeating values – think all kinds of statuses or 
>> good old Mr/Mrs/Miss. GIN only stores every unique column value only once as 
>> for the default B-tree you’ll have e.g. 1 millon leaf nodes with the integer 
>> “1” in it."
>> 
>> 
>> Does it mean that GIN is a very good choice for low cardinality columns.  
> 
> Not necessary. There is other index which also don’t keep column value in an 
> every leaf. Hash, for instance. 
> 
> For smallish values (which low cardinality columns tend to be) the per-tuple 
> overhead and the pointer itself is probably much larger than the value, so 
> hash won't save you much if any space.  The GIN index removes not just the 
> value, but the per-tuple overhead.  And also compresses the point list to 
> further save space.  
> 
> Here is a real-world example from one of my databases where each value is 
> about 17 characters long, and is present about 20 times:
> 
> gin: 411 MB
> btree: 2167 MB 
> hash: 2159 MB
> 
> Cheers,
> 
> Jeff

Re: WTF with hash index?

[Олег Самойлов]

Ah, thanks. I am not a developer of PostgreSQL. I am a developer in PostgreSQL. 
:) And I see two hash indexes on the same data and one of them 43 times bigger 
then other, this looked like something terribly wrong. Just free idea how to 
considerably improve your product.

> 13 нояб. 2018 г., в 22:37, Alvaro Herrera  
> написал(а):
> 
> On 2018-Nov-13, Олег Самойлов wrote:
> 
>> Very much better. What about to copy paste algorithm from
>> gin(jsonb_path_ops) to the hash index?
> 
> You're welcome to submit patches.
> 
> -- 
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Impact on PostgreSQL due to Redhat acquisition by IBM

[Ravi Krishna]

Well your information needs some update.

- On AIX, IBM had no issues selling Oracle, a rival to DB2.
- IBM Global Services, a consulting unit was the single biggest sales force for 
Oracle Installations outside
  Oracle. In other words, they ended up using Oracle for projects done by 
IGM-GS more than DB2.
  This was true some 10 yrs ago. Oracle use to point it out , with glee.
- Many years ago I was informed that Enterprise DB was funded by IBM.  Don't 
know how much it is true.

Re: Java UnsatisfiedLinkError exception when connecting to Postgresql database

[dclark]

 Adrian Klaver  wrote: 
> On 11/14/18 10:24 AM, dcl...@cinci.rr.com wrote:
> Please reply to list also.
> Ccing list.
> > 
> >  Adrian Klaver  wrote:
> >> On 11/14/18 9:25 AM, dcl...@cinci.rr.com wrote:
> >>> Hello;
> >>>
> >>> I've written a Java program which uses Postgresql via JDBC.  The program 
> >>> works fine on all RedHat systems I've tested except one, where it yields 
> >>> an UnsatisifiedLinkError.  Here is the stack trace:
> >>>
> >>> sun.misc.VM.latestUserDefinedLoader0(Native
> >>> Method)
> >>> sun.misc.VM.latestUserDefinedLoader(VM.java:411)
> >>> java.io.ObjectInputStream.latestUserDefinedLoader(ObjectInputStream.java:2351)
> >>> java.io.ObjectInputStream.resolveClass(ObjectInputStream.java:686)
> >>> java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1866)
> >>> java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1749)
> >>> java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:2040)
> >>> java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1571)
> >>> java.io.ObjectInputStream.readObject(ObjectInputStream.java:431)
> >>> org.postgresql.ds.common.BaseDataSource.readBaseObject(BaseDataSource.java:1210)
> >>> org.postgresql.ds.common.BaseDataSource.initializeFrom(BaseDataSource.java:1220)
> >>> org.postgresql.ds.PGPoolingDataSource.initialize(PGPoolingDataSource.java:267)
> >>> org.postgresql.ds.PGPoolingDataSource.getConnection(PGPoolingDataSource.java:324)
> >>>
> >>> Any ideas?
> >>
> >> What is different about the system that throws the error?
> >>
> >> For example:
> >>
> >> OS version
> >> JDBC version
> >> Postgres version
> >> Java version
> > 
> > Thank you for your reply.
> > 
> > OS on working system: Linux 3.10.0-693.11.6.el7.x86_64 x86_64
> > OS on problem system: Linux 3.10.0-693.21.1.el7.x86_64 x86_64
> > 
> > JDBC version on both systems: 9.4.1209
> > 
> > Postgres version on both systems: 9.6.5 on x86_64-redhat-linux-gnu, 
> > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
> > 
> > Java version on both systems:
> >   
> > openjdk version "1.8.0_171"
> > OpenJDK Runtime Environment (build 1.8.0_171-b10)
> > OpenJDK 64-Bit Server VM (build 25.171-b10, mixed mode)
> > 
> 
> Hmm.
> So what was the UnsatisifiedLinkError message, before the traceback above?

java.lang.UnsatisfiedLinkError: 
sun.misc.VM.latestUserDefinedLoader0()Ljava/lang/ClassLoader;

Thank you.

Re: libpq to JDBC adapter

[Dave Cramer]

Looks very interesting,

Cheers,

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Wed, 14 Nov 2018 at 14:57, Konstantin Knizhnik 
wrote:

> If somebody is interested in connection to various JDBC-compatible
> databases through postgres_fdw,
> please look at my pq2jdbc project: https://github.com/postgrespro/pq2jdbc
> Details of the project are in README file.
>
> If somebody can find some other use cases for libpq to JDBC adapter,
> please let me know!
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

Re: Java UnsatisfiedLinkError exception when connecting to Postgresql database

[Adrian Klaver]

On 11/14/18 10:24 AM, dcl...@cinci.rr.com wrote:
Please reply to list also.
Ccing list.


 Adrian Klaver  wrote:

On 11/14/18 9:25 AM, dcl...@cinci.rr.com wrote:

Hello;

I've written a Java program which uses Postgresql via JDBC.  The program works 
fine on all RedHat systems I've tested except one, where it yields an 
UnsatisifiedLinkError.  Here is the stack trace:

sun.misc.VM.latestUserDefinedLoader0(Native
Method)
sun.misc.VM.latestUserDefinedLoader(VM.java:411)
java.io.ObjectInputStream.latestUserDefinedLoader(ObjectInputStream.java:2351)
java.io.ObjectInputStream.resolveClass(ObjectInputStream.java:686)
java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1866)
java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1749)
java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:2040)
java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1571)
java.io.ObjectInputStream.readObject(ObjectInputStream.java:431)
org.postgresql.ds.common.BaseDataSource.readBaseObject(BaseDataSource.java:1210)
org.postgresql.ds.common.BaseDataSource.initializeFrom(BaseDataSource.java:1220)
org.postgresql.ds.PGPoolingDataSource.initialize(PGPoolingDataSource.java:267)
org.postgresql.ds.PGPoolingDataSource.getConnection(PGPoolingDataSource.java:324)

Any ideas?


What is different about the system that throws the error?

For example:

OS version
JDBC version
Postgres version
Java version


Thank you for your reply.

OS on working system: Linux 3.10.0-693.11.6.el7.x86_64 x86_64
OS on problem system: Linux 3.10.0-693.21.1.el7.x86_64 x86_64

JDBC version on both systems: 9.4.1209

Postgres version on both systems: 9.6.5 on x86_64-redhat-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Java version on both systems:
  
openjdk version "1.8.0_171"

OpenJDK Runtime Environment (build 1.8.0_171-b10)
OpenJDK 64-Bit Server VM (build 25.171-b10, mixed mode)



Hmm.
So what was the UnsatisifiedLinkError message, before the traceback above?


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

Re: Java UnsatisfiedLinkError exception when connecting to Postgresql database

[Rob Sargent]

On 11/14/18 5:03 PM, dcl...@cinci.rr.com wrote:

 Adrian Klaver  wrote:

On 11/14/18 10:24 AM, dcl...@cinci.rr.com wrote:
Please reply to list also.
Ccing list.

 Adrian Klaver  wrote:

On 11/14/18 9:25 AM, dcl...@cinci.rr.com wrote:

Hello;

I've written a Java program which uses Postgresql via JDBC.  The program works 
fine on all RedHat systems I've tested except one, where it yields an 
UnsatisifiedLinkError.  Here is the stack trace:

sun.misc.VM.latestUserDefinedLoader0(Native
Method)
sun.misc.VM.latestUserDefinedLoader(VM.java:411)
java.io.ObjectInputStream.latestUserDefinedLoader(ObjectInputStream.java:2351)
java.io.ObjectInputStream.resolveClass(ObjectInputStream.java:686)
java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1866)
java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1749)
java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:2040)
java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1571)
java.io.ObjectInputStream.readObject(ObjectInputStream.java:431)
org.postgresql.ds.common.BaseDataSource.readBaseObject(BaseDataSource.java:1210)
org.postgresql.ds.common.BaseDataSource.initializeFrom(BaseDataSource.java:1220)
org.postgresql.ds.PGPoolingDataSource.initialize(PGPoolingDataSource.java:267)
org.postgresql.ds.PGPoolingDataSource.getConnection(PGPoolingDataSource.java:324)

Any ideas?

What is different about the system that throws the error?

For example:

OS version
JDBC version
Postgres version
Java version

Thank you for your reply.

OS on working system: Linux 3.10.0-693.11.6.el7.x86_64 x86_64
OS on problem system: Linux 3.10.0-693.21.1.el7.x86_64 x86_64

JDBC version on both systems: 9.4.1209

Postgres version on both systems: 9.6.5 on x86_64-redhat-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Java version on both systems:
   
openjdk version "1.8.0_171"

OpenJDK Runtime Environment (build 1.8.0_171-b10)
OpenJDK 64-Bit Server VM (build 25.171-b10, mixed mode)


Hmm.
So what was the UnsatisifiedLinkError message, before the traceback above?

java.lang.UnsatisfiedLinkError: 
sun.misc.VM.latestUserDefinedLoader0()Ljava/lang/ClassLoader;

Thank you.



Should OpenJDK be looking for a sun class?

Re: Impact on PostgreSQL due to Redhat acquisition by IBM

[Adrian Klaver]

On 11/13/18 11:04 PM, Sachin Kotwal wrote:

Hi PostgreSQL lovers,

I heard news that Redhat is going to acquired by IBM. IBM has its on 
database. And they have history of selling applications with their own 
hardware (software and hardware together).


As per my knowledge PostgreSQL community has better support for Redhat 
family than any other platforms.


Not seeing it:

https://www.postgresql.org/download/



Does community is going to support/focus more Debian platforms ?
Does community has any plan to switch their main supported platform?


There is no main supported platform. There is the source and there are 
packaging built off the source. The packaging is done by a diverse group 
of people. So for example the RPMS:


https://yum.postgresql.org/contact.php

Devrim Gündüz   EDB

Jeff Frost  PGX

Craig Ringer2ndQuadrant



Please share if any other plan.


--

Thanks and Regards,
Sachin Kotwal



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

Re: Impact on PostgreSQL due to Redhat acquisition by IBM

[Sachin Kotwal]

On Wed, Nov 14, 2018 at 7:08 PM Adrian Klaver 
wrote:

> On 11/13/18 11:04 PM, Sachin Kotwal wrote:
> > Hi PostgreSQL lovers,
> >
> > I heard news that Redhat is going to acquired by IBM. IBM has its on
> > database. And they have history of selling applications with their own
> > hardware (software and hardware together).
> >
> > As per my knowledge PostgreSQL community has better support for Redhat
> > family than any other platforms.
>
> Not seeing it:
>
> https://www.postgresql.org/download/
>
> >
> > Does community is going to support/focus more Debian platforms ?
> > Does community has any plan to switch their main supported platform?
>
> There is no main supported platform. There is the source and there are
> packaging built off the source. The packaging is done by a diverse group
> of people. So for example the RPMS:
>
> https://yum.postgresql.org/contact.php
>
> Devrim Gündüz   EDB
>
> Jeff Frost  PGX
>
> Craig Ringer2ndQuadrant
>
>
> > Please share if any other plan.
> >
> >
> > --
> >
> > Thanks and Regards,
> > Sachin Kotwal
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Looks like no one have clear idea yet and deal also not completely done.
Hope Redhat community support will continue same as earlier.

Let's wait until something will announce by community is coming days.

Thanks all for your inputs.

Regards,
Sachin

-- 

Thanks and Regards,
Sachin Kotwal

VM Instance to Google Cloud SQL Migration

[Sathish Kumar]

Hi Team,

We would like to migrate our Postgresql VM instance on Google Cloud
Platform to Google Cloud SQL with a minimal downtime. As I checked, we have
to export and import the SQL file and our database size is large and cannot
afford longer downtime.

Do any have solution to achieve this?.

Thanks & Regards,
Sathish Kumar.V

Re: Move cluster to new host, upgraded version

[Tom Lane]

Rich Shepard  writes:
>ps ax | grep postgres
> shows a number of processes, but psql tells me there's no server running,
> and there is no postmaster.opts or postmaster.pid in the data directory.

Perhaps those are associated with some other data directory?

You could try using lsof on one of them to see what its current working
directory is (or on Linux, examine /proc/NN/cwd).  lsof on whichever
one(s) is/are postmasters would also tell you what sockets they're
listening on.

regards, tom lane

Re: Move cluster to new host, upgraded version

[Rich Shepard]

On Tue, 13 Nov 2018, Adrian Klaver wrote:


Just realized the question I should have asked is:
How did you get the pg_dumpall file processed by Postgres?
In other words how did you do it without a password?


  As user postgres I entered the command
$ psql -f dump-all.sql

  In any case, I need to back up because I missed something when
initializing the cluster.

  ps ax | grep postgres

shows a number of processes, but psql tells me there's no server running,
and there is no postmaster.opts or postmaster.pid in the data directory.

  I can delete contents of the data directory and re-initdb, or re-install
the application and start from scratch.

Advice appreciated,

Rich

Re: Move cluster to new host, upgraded version

[Adrian Klaver]

On 11/14/18 6:58 AM, Rich Shepard wrote:

On Tue, 13 Nov 2018, Adrian Klaver wrote:


Just realized the question I should have asked is:
How did you get the pg_dumpall file processed by Postgres?
In other words how did you do it without a password?


   As user postgres I entered the command
$ psql -f dump-all.sql

   In any case, I need to back up because I missed something when
initializing the cluster.

   ps ax | grep postgres

shows a number of processes, but psql tells me there's no server running,
and there is no postmaster.opts or postmaster.pid in the data directory.

   I can delete contents of the data directory and re-initdb, or re-install
the application and start from scratch.


It was running when you did this:

psql -f dump-all.sql

correct?

Seems to me it is a start up script issue.

Have you rebooted the computer since the last time Postgres ran?

Is there a startup script in init.d/ or where ever your scripts are?

Can you start the server manually using pg_ctl?



Advice appreciated,

Rich





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

Re: Move cluster to new host, upgraded version

[Rich Shepard]

On Wed, 14 Nov 2018, Tom Lane wrote:


Perhaps those are associated with some other data directory?


Tom/Adrian,

  I think that I FUBARed the upgrade from 10.3 to 11.1. I've removed both
packages and am rebuilding 11.1. I'll install it, run initdb on it, then
re-run pg_dumpall on the 10.5 data directory on the old desktop. Starting
from scratch on this new desktop will save time for all of us.

Thanks,

Rich

Re: Move cluster to new host, upgraded version [DONE]

[Rich Shepard]

On Wed, 14 Nov 2018, Rich Shepard wrote:


I think that I FUBARed the upgrade from 10.3 to 11.1. I've removed both
packages and am rebuilding 11.1. I'll install it, run initdb on it, then
re-run pg_dumpall on the 10.5 data directory on the old desktop. Starting
from scratch on this new desktop will save time for all of us.


  All fixed now. To complete the thread this is what I did:

  1) Removed Slackware packages for postgresql-10.3 and -11.1.
  2) Removed /var/lib/pgsql/10.3 and 11.1.
  3) Re-built postgresql-11.1 and re-installed it.
  4) As user 'postgres' ran initdb and pg_ctl start pointing to
/var/lib/pgsql/11/data.
  5) Edited postgresql.conf and pg_hba.conf to accept connections from all
hosts on the LAN.
  6) Re-started postgres.
  7) As 'postgre' ran
pg_dumpall -h salmo -c -f pg-all-2018-11-14.sql
  8) Then postgres ran
psql -f pg-all-2018-11-14.sql

  Now I, as a user, can access my databases without a password.

  Thank you, Adrian, for your patient help. And you, Tom, for your helpful
comment.

Best regards,

Rich

<-> Operator on Trigram Index

[Jeffrey Kamei]

I'm trying to get the <-> operator to recognize a trigram index (GIST) I've
set on a table. Using `EXPLAIN VERBOSE` I can see the query engine ignoring
the trigram index when using the `<->` operator. However if I use the `%`
operator, the index is found and used. Can you explain why this is
happening? As far as I can tell from the documentation, the `<->` operator
should be using the index as well.

Java UnsatisfiedLinkError exception when connecting to Postgresql database

[dclark]

Hello;

I've written a Java program which uses Postgresql via JDBC.  The program works 
fine on all RedHat systems I've tested except one, where it yields an 
UnsatisifiedLinkError.  Here is the stack trace:

sun.misc.VM.latestUserDefinedLoader0(Native
Method)
sun.misc.VM.latestUserDefinedLoader(VM.java:411)
java.io.ObjectInputStream.latestUserDefinedLoader(ObjectInputStream.java:2351)
java.io.ObjectInputStream.resolveClass(ObjectInputStream.java:686)
java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1866)
java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1749)
java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:2040)
java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1571)
java.io.ObjectInputStream.readObject(ObjectInputStream.java:431)
org.postgresql.ds.common.BaseDataSource.readBaseObject(BaseDataSource.java:1210)
org.postgresql.ds.common.BaseDataSource.initializeFrom(BaseDataSource.java:1220)
org.postgresql.ds.PGPoolingDataSource.initialize(PGPoolingDataSource.java:267)
org.postgresql.ds.PGPoolingDataSource.getConnection(PGPoolingDataSource.java:324)

Any ideas?

Thanks in advance.

Default Privilege Table ANY ROLE

[Nicolas Paris]

Hi

I d'like my user be able to select on any new table from other users.

> ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT  
> select ON TABLES TO "myuser"
> ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT  
> select ON TABLES TO "myuser"
> ...


Do I really have to repeat the command for all users ?

The problem is I have many user able to create tables and all of them
have to read each other. 

Thanks



-- 
nicolas

Re: Default Privilege Table ANY ROLE

[Laurenz Albe]

Nicolas Paris wrote:
> I d'like my user be able to select on any new table from other users.
> 
> > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT 
> >  select ON TABLES TO "myuser"
> > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT 
> >  select ON TABLES TO "myuser"
> > ...
> 
> 
> Do I really have to repeat the command for all users ?
> 
> The problem is I have many user able to create tables and all of them
> have to read each other. 

This is one setup that I can come up with:

CREATE ROLE tableowner NOINHERIT;
CREATE ROLE tablereader;
ALTER DEFAULT PRIVILEGES FOR ROLE tableowner IN SCHEMA myschema GRANT SELECT ON 
TABLES TO tablereader;

CREATE ROLE alice LOGIN IN ROLE tableowner, tablereader;
CREATE ROLE bob LOGIN IN ROLE tableowner, tablereader;

Now whenever "alice" has to create a table, she runs

SET ROLE tableowner;
CREATE TABLE myschema.newtable(x integer);
RESET ROLE;

Then all these tables belong to "tableowner", and each user in group 
"tablereader"
can SELECT from them:

\z myschema.newtable 
 Access privileges
  Schema  |   Name   | Type  |   Access privileges   | Column 
privileges | Policies 
--+--+---+---+---+--
 myschema | newtable | table | tableowner=arwdDxt/tableowner+|  
 | 
  |  |   | tablereader=r/tableowner  |  
 | 
(1 row)

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

Re: Default Privilege Table ANY ROLE

[Nicolas Paris]

On Wed, Nov 14, 2018 at 09:04:44PM +0100, Laurenz Albe wrote:
> Nicolas Paris wrote:
> > I d'like my user be able to select on any new table from other users.
> > 
> > > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" 
> > > GRANT  select ON TABLES TO "myuser"
> > > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" 
> > > GRANT  select ON TABLES TO "myuser"
> > > ...
> > 
> > 
> > Do I really have to repeat the command for all users ?
> > 
> > The problem is I have many user able to create tables and all of them
> > have to read each other. 
> 
> Now whenever "alice" has to create a table, she runs
> SET ROLE tableowner;
> Then all these tables belong to "tableowner", and each user in group 
> "tablereader"
> can SELECT from them:

Yes, this step is overhead to me:
> SET ROLE tableowner;

In my mind, both bob/alice inherit from the same group, so they should
share the table they build according to this:

> ALTER DEFAULT PRIVILEGES FOR ROLE tableowner IN SCHEMA myschema GRANT SELECT 
> ON TABLES TO tablereader;




-- 
nicolas

Re: TOAST : partial detoasting : only a small part of a toasted value (for pgpointcloud)

[Rémi Cura]

wow,
it was right under my nose.
Thank you very much !
Cheers,
Remi-C

Le mar. 13 nov. 2018 à 19:00, Tom Lane  a écrit :

> =?UTF-8?Q?R=C3=A9mi_Cura?=  writes:
> > So the pgpointcloud store sometimes very large groups of points into one
> > row (TOASTED), something along few kB to few MB. TOAST would be in
> EXTERNAL
> > mode (no compression).
> > Sometimes we only want to access a part of this data (one or several
> blocks
> > within the full data).
> >> From what I  understand, for the moment it requires to fetch all the
> data
> > from disk, then de-toast it, then select only the part of the data we are
> > interested in.
>
> > Yet I think it is possible to detoast only a subset of the data (and thus
> > fetch only some part of the data), considering that the [doc on toast](
> > https://www.postgresql.org/docs/11/storage-toast.html) says
> >> " Use of EXTERNAL will make substring operations on wide text and bytea
> > columns faster
> >> (at the penalty of increased storage space) because these operations are
> > optimized
> >> to fetch only the required parts of the out-of-line value when it is not
> > compressed. "
>
> > So my question is how does it work, how easy would it be to implement for
> > pgpointcloud?
>
> See PG_DETOAST_DATUM_SLICE and users of that macro.
>
> regards, tom lane
>

Re: Default Privilege Table ANY ROLE

[Tom Lane]

Maybe I'm missing something, but doesn't this solve your problem
as stated?

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;

regards, tom lane

Re: pg_dump out of memory for large table with LOB

[Tom Lane]

Jean-Marc Lessard  writes:
> Would you recommend bytea over LOB considering that the max LOB size is well 
> bellow 1GB?

Yes, probably.  The reason that pg_dump has trouble with lots of small
BLOBs is the 9.0-era decision to treat BLOBs as independent objects
having their own owners, privilege attributes, and archive TOC entries
--- it's really the per-BLOB TOC entries that are causing the issue
for you here.  That model is fine as long as BLOBs are, uh, large.
If you're using them as replacements for bytea, the overhead is going
to be prohibitive.

regards, tom lane

Re: pg_dump out of memory for large table with LOB

[Ron]

On 11/14/2018 11:14 AM, Jean-Marc Lessard wrote:

Adrien Nayrat wrote:
> With 17 million LO, it could eat lot of memory ;)
Yes it does.

I did several tests and here are my observations.

First memory settings are:
shared_buffers = 3GB
work_mem = 32Mb
maintenance_work_mem = 1GB
effective_cache_size = 9MB
bytea_output = 'escape'


Why escape instead of hex?


--
Angular momentum makes the world go 'round.

Re: Default Privilege Table ANY ROLE

[Nicolas Paris]

On Wed, Nov 14, 2018 at 03:53:39PM -0500, Tom Lane wrote:
> Maybe I'm missing something, but doesn't this solve your problem
> as stated?
> 
> ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;


Not sure that's equivalent to what I am looking for below (but is not allowed):

> ALTER DEFAULT PRIVILEGES  FOR  ROLE  *.* IN SCHEMA "myschema" GRANT  select 
> ON TABLES TO "myuser"

-- 
nicolas