Re: logical replication snapshots

On 2018-07-25 12:31:01 -0700, Adrian Klaver wrote: > On 07/25/2018 11:10 AM, Dimitri Maziuk wrote: > > On 07/25/2018 11:16 AM, Adrian Klaver wrote: > > > > > Where you using pg_export_snapshot() by any chance?: > > > > If it's not in chapter 31 of TFM then I have not touched it. The only > >

Re: logical replication snapshots

On 07/25/2018 11:10 AM, Dimitri Maziuk wrote: On 07/25/2018 11:16 AM, Adrian Klaver wrote: Where you using pg_export_snapshot() by any chance?: If it's not in chapter 31 of TFM then I have not touched it. The only notable thing I changed from 31.9 quickstart is that replica identity is full

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

On 2018-07-25 19:27:47 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2018-06-28 08:02:10 -0700, Andres Freund wrote: > >> I wonder why we don't just generally trigger invalidations to an > >> indexes' "owning" relation in CacheInvalidateHeapTuple()? > > > Tom, do you have any comments

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

On Wed, Jul 25, 2018 at 4:03 PM, Andres Freund wrote: > Peter, given that your patch made this more likely, and that you're a > committer these days, I'm opening an open items entry, and assign it to > you. Does that sound ok? I intend to follow through on this soon. I have been distracted by

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

Andres Freund writes: > On 2018-06-28 08:02:10 -0700, Andres Freund wrote: >> I wonder why we don't just generally trigger invalidations to an >> indexes' "owning" relation in CacheInvalidateHeapTuple()? > Tom, do you have any comments about the above? It seems like an ugly and fragile hack,

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

Hi, On 2018-06-28 08:02:10 -0700, Andres Freund wrote: > I believe this happens because there's currently no relcache > invalidation registered for the main relation, until *after* the index > is built. Normally it'd be the CacheInvalidateRelcacheByTuple(tuple) in > index_update_stats(), which is

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

Hi, On 2018-07-09 12:06:21 -0700, Peter Geoghegan wrote: > > I assume we'll have to backpatch this issue, so I think it'd probably a > > good idea to put a specific CacheInvalidateHeapTuple() in there > > explicitly in the back branches, and do the larger fix in 12. ISTM > > there's some risks

Re: logical replication snapshots

On 07/25/2018 02:31 PM, Adrian Klaver wrote: > In your previous post you said: > > "(To somebody else as I'm obviously not turning logical replication back > on until I know it won't kill my server again.) " > > So what are you working off now? I have that database rebuilt with no publications

Re: logical replication snapshots

On 07/25/2018 11:10 AM, Dimitri Maziuk wrote: On 07/25/2018 11:16 AM, Adrian Klaver wrote: Where you using pg_export_snapshot() by any chance?: If it's not in chapter 31 of TFM then I have not touched it. The only notable thing I changed from 31.9 quickstart is that replica identity is full

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

That does it. Good enough, despite the non-white space wrapping thing. Thanks ! On Wed, Jul 25, 2018 at 12:53 PM, Adrian Klaver wrote: > On 07/25/2018 09:40 AM, David Gauthier wrote: > >> Hi: >> >> psql (9.6.7, server 9.1.9) on RHEL6 >> >> In order to avoid record wrapping in the tabular

Re: logical replication snapshots

On 07/25/2018 11:16 AM, Adrian Klaver wrote: > Where you using pg_export_snapshot() by any chance?: If it's not in chapter 31 of TFM then I have not touched it. The only notable thing I changed from 31.9 quickstart is that replica identity is full on all tables (the data's a mess). > Where

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

On 07/25/2018 09:40 AM, David Gauthier wrote: Hi: psql (9.6.7, server 9.1.9)  on RHEL6 In order to avoid record wrapping in the tabular output of a "select" statement, I need to limit the width of certain columns.  For those columns, I would like to have text wrapping so as not to lose any

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

On 2018-Jul-25, David Gauthier wrote: > Hi: > > psql (9.6.7, server 9.1.9) on RHEL6 > > In order to avoid record wrapping in the tabular output of a "select" > statement, I need to limit the width of certain columns. For those > columns, I would like to have text wrapping so as not to lose

Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

Hi: psql (9.6.7, server 9.1.9) on RHEL6 In order to avoid record wrapping in the tabular output of a "select" statement, I need to limit the width of certain columns. For those columns, I would like to have text wrapping so as not to lose any information (IOW, I don't want to simply truncatate

Re: Permission denied on schema for all users on insert to table with fk

On 07/25/2018 06:40 AM, Leland Weathers wrote: I just ran into an issue on 9.5.13 after creating a new schema with a set of tables in them, that no accounts (including schema / table owners) can insert into a table with a fk relation. A snippet of the error output in log files is as follows

Permission denied on schema for all users on insert to table with fk

I just ran into an issue on 9.5.13 after creating a new schema with a set of tables in them, that no accounts (including schema / table owners) can insert into a table with a fk relation. A snippet of the error output in log files is as follows and looks like it is permissions related to the

Re: Order in which tables are dumped

On 07/25/2018 10:43 AM, Vick Khera wrote: On Wed, Jul 25, 2018 at 11:15 AM, Ron > wrote: Hi, v8.4 if it matters. It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after*

Re: logical replication snapshots

On 07/25/2018 08:40 AM, Dimitri Maziuk wrote: On 7/25/2018 10:28 AM, Andres Freund wrote: Are you really expecting us to be able to reproduce the problem based on the above description? Our test suites do setup plain replications setups, and the problem doesn't occur there. I don't, by

Re: Order in which tables are dumped

On 07/25/2018 10:28 AM, Tom Lane wrote: Ron writes: It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after* "doc_image". Looks like standard C-locale (ASCII) sort order to me ... I hate spreadsheets.  And gnu sort,

Re: Order in which tables are dumped

On Wed, Jul 25, 2018 at 11:15 AM, Ron wrote: > Hi, > > v8.4 if it matters. > > It looked like the tables were being backed up in alphanumeric order, but > now I see that table "docformat" is being dumped *after* "doc_image". > > Are there some other rules besides alphabetical sorting? > Is

Re: logical replication snapshots

On 7/25/2018 10:28 AM, Andres Freund wrote: Are you really expecting us to be able to reproduce the problem based on the above description? Our test suites do setup plain replications setups, and the problem doesn't occur there. I don't, by definition, have a reproducible case: it only

Re: logical replication snapshots

On 2018-07-24 14:02:26 -0500, Dimitri Maziuk wrote: > On 07/24/2018 01:43 PM, Andres Freund wrote: > > On 2018-07-24 12:22:24 -0500, Dimitri Maziuk wrote: > >> On 07/24/2018 12:21 PM, Alvaro Herrera wrote: > >>> > >>> Are you serious about us trying to diagnose a bug with this description? > >> >

Re: Order in which tables are dumped

Ron writes: > It looked like the tables were being backed up in alphanumeric order, but > now I see that table "docformat" is being dumped *after* "doc_image". Looks like standard C-locale (ASCII) sort order to me ... > Are there some other rules besides alphabetical sorting? Dependencies can

Order in which tables are dumped

Hi, v8.4 if it matters. It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after* "doc_image". Are there some other rules besides alphabetical sorting? -- Angular momentum makes the world go 'round.

Re: Restore from dumps

yeah, we updated that function in production to says public.all_days. I will let you know at the next restore. Thanks guys, appreciated. 2018-07-25 16:28 GMT+02:00 Tom Lane : > Laurenz Albe writes: > > Nicola Contu wrote: > >> pg_restore: [archiver (db)] could not execute query: ERROR:

Re: Restore from dumps

Laurenz Albe writes: > Nicola Contu wrote: >> pg_restore: [archiver (db)] could not execute query: ERROR: relation >> "all_days" does not exist >> LINE 3: from all_days >> ^ >> QUERY: >> select count(*)::numeric >> from all_days >> where (("date" between $2::date and $1::date) or ("date"

Re: DB Backup from WAL Slave

Hi Basti, When you are dumping a big table using hot_standby_feedback=on,you are holding the master from cleaning up old records(as in: it was running on master). If too much time elapses while the lock is held (30 secs by default), then the statementis cancelled. At the expense of

Re: DB Backup from WAL Slave

Thanks a lot for this tip. I don't know is this is the right param. Becouse of: "... , but can cause database bloat on the primary for some workloads." This the an other way to backup the database and work around pg_dump: Error message from server: ERROR: canceling statement due to conflict

Re: Order of execution for permissive RLS policies

On 24 July 2018 at 15:25, Simon Brent wrote: > I've been using postgres for a while now, and have just started looking in > to row level security. I have found something that I think is a bit strange, > and wanted to know if anyone knows how/why it is the case. > > I have a table with multiple

Re: Read only to schema

Hi ! I have user : [image: image.png] and this user can not login to Database AccessLog. I tried to use: GRANT CONNECT ON DATABASE " AccessLog" TO "Luke"; GRANT CREATE ON SCHEMA PUBLIC TO "Luke"; GRANT USAGE ON SCHEMA public TO "Luke" So expecting result : can modyfy DDL and DML in

Re: Restore from dumps

Thanks Laurenz I will try that. Btw, just wondering why it works if I refresh it later, even if the definition is still without public 2018-07-25 12:06 GMT+02:00 Laurenz Albe : > Nicola Contu wrote: > > we recently moved from postgres 9.6.6 to 10.4 > > > > We perform a pg_dump in production to

Re: Restore from dumps

Nicola Contu wrote: > we recently moved from postgres 9.6.6 to 10.4 > > We perform a pg_dump in production to restore daily in a preprod env. > This process used to work perfectly, but now we have a tiny problem. > > We first restore data, we perform a vacuum and then we restore matviews. >

Re: Restore from dumps

Hello these are the commands we use pretty much: tar -xf tarname.tar -C /backupdatadir --strip-components=4 pg_restore -l /backupdatadir | sed '/MATERIALIZED VIEW DATA/d' > /restore.lst pg_restore -U postgres -L /restore.lst -d DBNAME -j 32 /backupdatadir vacuumdb --analyze-in-stages -U

Re: Restore from dumps

> On 25 Jul 2018, at 9:43, Nicola Contu wrote: > > Hello, > we recently moved from postgres 9.6.6 to 10.4 > > We perform a pg_dump in production to restore daily in a preprod env. > This process used to work perfectly, but now we have a tiny problem. > > We first restore data, we perform a

Restore from dumps

Hello, we recently moved from postgres 9.6.6 to 10.4 We perform a pg_dump in production to restore daily in a preprod env. This process used to work perfectly, but now we have a tiny problem. We first restore data, we perform a vacuum and then we restore matviews. Restoring matviews now we have