Re: [GENERAL] Multiple Schemas vs. Multiple Databases

Melvin Davidson schrieb am 13.10.2017 um 21:42: If that is what you need to do, then definitely use multiple schemas. In PostgreSQL, the only way to do cross db queries / DML, is with the dblink extension, and from personal use, it is a PIA to use. dblink is not the only way to do that.

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

On 10/13/2017 12:47 PM, John R Pierce wrote: On 10/13/2017 12:29 PM, Igal @ Lucee.org wrote: The main things that I need to do is:   a) Be able to backup/restore each "part" separately.  Looks like pg_dump allows that for schemas via the --schema=schema argument.   b) Be able to query

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

On 10/13/2017 12:29 PM, Igal @ Lucee.org wrote: I have read quite a few articles about multiple schemas vs. multiple databases, but they are all very generic so I wanted to ask here for a specific use case: I am migrating a Web Application from MS SQL Server to PostgreSQL.  For the sake of

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

On Fri, Oct 13, 2017 at 3:29 PM, Igal @ Lucee.org wrote: > Hello, > > I have read quite a few articles about multiple schemas vs. multiple > databases, but they are all very generic so I wanted to ask here for a > specific use case: > > I am migrating a Web Application from MS

Re: [GENERAL] REASSIGN OWNED simply doesn't work

On Fri, Oct 13, 2017 at 6:04 AM, Alvaro Herrera wrote: > Sam Gendler wrote: > > psql 9.6.3 on OS X. > > > > I'm dealing with a production database in which all db access has been > made > > by the same user - the db owner, which isn't actually a superuser because > > the

[GENERAL] Multiple Schemas vs. Multiple Databases

Hello, I have read quite a few articles about multiple schemas vs. multiple databases, but they are all very generic so I wanted to ask here for a specific use case: I am migrating a Web Application from MS SQL Server to PostgreSQL.  For the sake of easier maintenance, on SQL Server I have

Re: [GENERAL] REASSIGN OWNED simply doesn't work

Sam Gendler wrote: > psql 9.6.3 on OS X. > > I'm dealing with a production database in which all db access has been made > by the same user - the db owner, which isn't actually a superuser because > the db runs on amazon RDS - amazon retains the superuser privilege for its > own users and makes

Re: [GENERAL] Fwd: [BUGS] BUG #14850: Implement optinal additinal parameter for 'justify' date/time function

huh... how did I miss `age` function??? Thanks. I give me the idea. I will add 5days when the month of day is greater or equal to 28 select age( '2016-02-29'::date +'5days'::interval, '2016-01-31'::date+'5days'::interval );  age  --- 1 mon(1 row) It seems resolves my issue  12.10.2017, 17:38,

Re: [GENERAL] Permissions for Web App

On 10/13/2017 11:21 AM, David G. Johnston wrote: On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org >wrote: You mean that if I execute the ALTER DEFAULT command above as user `postgres` then only tables created by user `postgres` will give

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017, at 03:16 PM, David G. Johnston wrote: > implement a "system-managed-enum" type with many of the same properties [...] > TOAST does involved compression but the input to > the compression algorithm is a single cell (row and column) in a table.​ > As noted above I consider the

Re: [GENERAL] "Shared strings"-style table

Seamus Abshere schrieb am 13.10.2017 um 18:43: On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: Theoretically / blue sky, could there be a table or column type that transparently handles "shared strings" like this, reducing size on disk at the cost of lookup overhead for all queries? (I

Re: [GENERAL] Permissions for Web App

On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org wrote: > You mean that if I execute the ALTER DEFAULT command above as user > `postgres` then only tables created by user `postgres` will give default > privileges to role `webapp`? > ​Yes. "​You can change default privileges

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017 at 9:29 AM, Seamus Abshere wrote: > > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote > > > Theoretically / blue sky, could there be a table or column type that > > > transparently handles "shared strings" like this, reducing size on disk > > > at

Re: [GENERAL] Permissions for Web App

Stephen, On 10/10/2017 6:14 AM, Stephen Frost wrote: For future reference and for the benefit of others, the command that I ran is:   ALTER DEFAULT PRIVILEGES IN SCHEMA public     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp; Note that DEFAULT PRIVILEGES apply to a specific

Re: [GENERAL] "Shared strings"-style table

On 2017-10-13 12:49:21 -0300, Seamus Abshere wrote: > In the spreadsheet world, there is this concept of "shared strings," a > simple way of compressing spreadsheets when the data is duplicated in > many cells. > > In my database, I have a table with >200 million rows and >300 columns > (all the

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017 at 12:52 PM, Melvin Davidson wrote: > > > On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere > wrote: > >> > > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: >> > >> Theoretically / blue sky, could there be a table or column

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere wrote: > > > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: > > >> Theoretically / blue sky, could there be a table or column type that > > >> transparently handles "shared strings" like this, reducing size on > disk >

Re: [GENERAL] Merge - Insert Select

On Fri, Oct 13, 2017 at 9:39 AM, Susan Hurst wrote: > Does postgres have a MERGE statement ala Oracle? No. > I have found examples online for INSERT...ON CONFLICT DO NOTHING, but all of > those examples use VALUES statements, which is not the scenario that I

Re: [GENERAL] "Shared strings"-style table

> > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: > >> Theoretically / blue sky, could there be a table or column type that > >> transparently handles "shared strings" like this, reducing size on disk > >> at the cost of lookup overhead for all queries? > >> (I guess maybe it's like

[GENERAL] Merge - Insert Select

What is the best practice for doing an INSERT...SELECT statement for new values only. Existing values must be excluded from the insert. Does postgres have a MERGE statement ala Oracle? I have found examples online for INSERT...ON CONFLICT DO NOTHING, but all of those examples use VALUES

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017 at 12:12 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere > wrote: > >> Theoretically / blue sky, could there be a table or column type that >> transparently handles "shared strings" like this,

Re: [GENERAL] "Shared strings"-style table

> On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote > > Theoretically / blue sky, could there be a table or column type that > > transparently handles "shared strings" like this, reducing size on disk > > at the cost of lookup overhead for all queries? > > (I guess maybe it's like TOAST, but

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: > Theoretically / blue sky, could there be a table or column type that > transparently handles "shared strings" like this, reducing size on disk > at the cost of lookup overhead for all queries? > > (I guess maybe it's

Re: [GENERAL] "Shared strings"-style table

On 10/13/2017 09:49 AM, Seamus Abshere wrote: hey, In the spreadsheet world, there is this concept of "shared strings," a simple way of compressing spreadsheets when the data is duplicated in many cells. In my database, I have a table with >200 million rows and >300 columns (all the

[GENERAL] "Shared strings"-style table

hey, In the spreadsheet world, there is this concept of "shared strings," a simple way of compressing spreadsheets when the data is duplicated in many cells. In my database, I have a table with >200 million rows and >300 columns (all the households in the United States). For clarity of

Re: [GENERAL] Restore LargeObjects on different server

Durumdara wrote: > The pg_catalog schema is system schema, but it is IN the DB. > > Is this true? So OID is not global (out from DB)? The OID generator is global to the instance, but the unicity checks are local to the tables that use OIDs, including large objects. The case when you

[GENERAL] Is pgbouncer still maintained?

Hi all, I hope I'm posting this in the correct place. We use pgbouncer, as I'm sure many other people do, but we are becoming increasingly worried by the lack of a new release since February 2016 and a slowdown in development activity on the master branch. https://github.com/pgbouncer/pgbouncer

Re: [GENERAL] Index corruption & broken clog

I seem that a failed vmotion occured in the same time frame.

Re: [GENERAL] Index corruption & broken clog

Thanks for the help. > Shut down the corrupted database and make a physical backup of it. > I did it before trying anything. > Check the disks and memory on the machine where the problem occurred. > We are using virtual machines (VMWare). I will ask then to investigate anyway. > You can

Re: [GENERAL] Index corruption & broken clog

Benoit Lobréau wrote: > One of my PostgreSQL server crashed badly yesterday. A process was killed > (see dmesg below) and postgres was stuck with theses process: > > postgres  2083     1  0 Oct08 ?        00:19:02 > /usr/lib/postgresql/9.5/bin/postgres -D /home/postgres/data/i090/systeme >