Re: [HACKERS] DROP OWNED BY ... CACADE & "could not open relation with OID" error

2016-07-21 13:53 GMT+09:00 Alvaro Herrera : > Satoshi Nagayasu wrote: >> Hi, >> >> I have been trying MADlib [1], a machine-learning library for PostgreSQL, >> and when I was tying it on 9.5 and 9.6beta2, I often got following >> error on my box. >> >>

[HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

Folks, Please find attached a patch which makes it possible to disallow UPDATEs and DELETEs which lack a WHERE clause. As this changes query behavior, I've made the new GUCs PGC_SUSET. What say? Thanks to Gurjeet Singh for the idea and Andrew Gierth for the tips implementing. Best, David. --

Re: [HACKERS] DROP OWNED BY ... CACADE & "could not open relation with OID" error

Satoshi Nagayasu wrote: > Hi, > > I have been trying MADlib [1], a machine-learning library for PostgreSQL, > and when I was tying it on 9.5 and 9.6beta2, I often got following > error on my box. > > > madpack.py : ERROR : SQL command failed: > SQL: DROP

Re: [HACKERS] Declarative partitioning

On 2016/07/19 22:53, Ashutosh Bapat wrote: > I am seeing following warning with this set of patches. > gram.y:4734:24: warning: assignment from incompatible pointer type [enabled > by default] Thanks, will fix. Was a copy-paste error. Thanks, Amit -- Sent via pgsql-hackers mailing list

[HACKERS] DROP OWNED BY ... CACADE & "could not open relation with OID" error

Hi, I have been trying MADlib [1], a machine-learning library for PostgreSQL, and when I was tying it on 9.5 and 9.6beta2, I often got following error on my box. madpack.py : ERROR : SQL command failed: SQL: DROP OWNED BY madlib_19_installcheck CASCADE;

Re: [HACKERS] Design for In-Core Logical Replication

On 21 July 2016 at 11:05, Joshua D. Drake wrote: > On 07/20/2016 06:35 PM, Craig Ringer wrote: > > First, I'd like to emphasise that logical replication has been stalled >> for ages now because we can no longer make forward progress on core >> features needed until we

Re: [HACKERS] [BUG] pg_basebackup from disconnected standby fails

On Thu, Jul 21, 2016 at 11:56 AM, Amit Kapila wrote: > On Thu, Jul 21, 2016 at 7:28 AM, Michael Paquier > wrote: >> On Wed, Jul 20, 2016 at 8:56 PM, Michael Paquier >> wrote: Yeah, I think that is totally

Re: [HACKERS] Design for In-Core Logical Replication

On 07/20/2016 06:35 PM, Craig Ringer wrote: First, I'd like to emphasise that logical replication has been stalled for ages now because we can no longer make forward progress on core features needed until we have in-core logical replication (they're dismissed as irrelevant, no in core users,

Re: [HACKERS] [BUG] pg_basebackup from disconnected standby fails

On Thu, Jul 21, 2016 at 7:28 AM, Michael Paquier wrote: > On Wed, Jul 20, 2016 at 8:56 PM, Michael Paquier > wrote: >>> >>> Yeah, I think that is totally different angle to fix this issue, so >>> don't you think it is better to start a

Re: [HACKERS] [BUG] pg_basebackup from disconnected standby fails

On Wed, Jul 20, 2016 at 8:56 PM, Michael Paquier wrote: > On Wed, Jul 20, 2016 at 8:40 PM, Amit Kapila wrote: >> On Wed, Jul 20, 2016 at 5:12 AM, Michael Paquier >> wrote: >>> On Tue, Jul 19, 2016 at 9:08 PM, Amit

Re: [HACKERS] Design for In-Core Logical Replication

On 21 July 2016 at 01:20, Simon Riggs wrote: > On 20 July 2016 at 17:52, Rod Taylor wrote: > > >> I think it's important for communication channels to be defined >> separately from the subscriptions. >> > > I agree and believe it will be that way. >

[HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Tue, Jul 19, 2016 at 09:01:05PM -0400, Noah Misch wrote: > On Tue, Jul 19, 2016 at 06:09:59PM -0500, Kevin Grittner wrote: > > On Mon, Jul 18, 2016 at 9:10 PM, Noah Misch wrote: > > > On Sat, Jul 16, 2016 at 06:48:08PM -0400, Noah Misch wrote: > > >> This PostgreSQL 9.6 open

Re: [HACKERS] skink's test_decoding failures in 9.4 branch

Andres Freund writes: > I guess either using valgrind's gdb server on error, or putting some > asserts checking the size would be best. I can look into it, but it'll > not be today likely. I believe the problem is that DecodeUpdate is not on the same page as the WAL-writing

Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol

On Thu, Jul 21, 2016 at 5:25 AM, David Fetter wrote: > On Wed, Jul 20, 2016 at 02:12:57PM -0400, Alvaro Herrera wrote: >> Michael Paquier wrote: >> > On Wed, Jul 6, 2016 at 4:18 PM, Michael Paquier >> > wrote: >> > > OK, after hacking that for a bit I

Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol

On Thu, Jul 21, 2016 at 12:15 AM, Robert Haas wrote: > On Fri, Jul 15, 2016 at 9:30 AM, Michael Paquier > wrote: >> OK, I am doing that at the end. >> >> And also while moving on... >> >> On another topic, here are some ideas to extend

Re: [HACKERS] Design for In-Core Logical Replication

On 20/07/16 19:07, Simon Riggs wrote: On 20 July 2016 at 16:39, Joshua D. Drake > wrote: Logical Replication uses a Publish and Subscribe model with one or more Subscribers subscribing to one

Re: [HACKERS] Odd error when using UNION and COLLATE

On Wed, Jul 20, 2016 at 06:03:08PM -0400, Tom Lane wrote: > Greg Stark writes: > > But I think I agree that it's surprising that the collate clause isn't > > working in the ORDER BY on a column produced by a UNION. Certainly > > that's where people usually want to put it. > > See

Re: [HACKERS] Odd error when using UNION and COLLATE

On Wed, Jul 20, 2016 at 10:55:38PM +0100, Greg Stark wrote: > On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian wrote: > > SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C"; > > > ::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ; Oh,

Re: [HACKERS] Odd error when using UNION and COLLATE

Greg Stark writes: > But I think I agree that it's surprising that the collate clause isn't > working in the ORDER BY on a column produced by a UNION. Certainly > that's where people usually want to put it. See this ancient comment in transformSetOperationStmt: * For now, we

Re: [HACKERS] Odd error when using UNION and COLLATE

Actually there's nothing about UNION here. It's true for any column alias: ::***> select 'a-c' AS x ORDER BY x COLLATE "C" ; ERROR: 42703: column "x" does not exist LINE 2: select 'a-c' AS x ORDER BY x COLLATE "C" ; ^ LOCATION: errorMissingColumn,

Re: [HACKERS] Odd error when using UNION and COLLATE

On Wed, Jul 20, 2016 at 5:38 PM, Bruce Momjian wrote: > I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and > we don't allow a_expr in a UNION. Perhaps we are too strict here, but I > can't tell. > ​ORDER BY 1 COLLATE "C" is indeed an expression - the

Re: [HACKERS] Odd error when using UNION and COLLATE

On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian wrote: > SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C"; ::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ; ┌─┐ │ x │ ├─┤ │ a-c │ │ ab │ └─┘ (2 rows) But I think I

[HACKERS] Odd error when using UNION and COLLATE

Seems you can't use UNION and COLLATE in the same SELECT statement; you have to put the UNION inside of WITH and then do the COLLATE outside: test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C"; ERROR: collations are not supported by type integer LINE 1: ... 'a-c' AS x

Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol

On Wed, Jul 20, 2016 at 02:12:57PM -0400, Alvaro Herrera wrote: > Michael Paquier wrote: > > On Wed, Jul 6, 2016 at 4:18 PM, Michael Paquier > > wrote: > > > OK, after hacking that for a bit I have finished with option 2 and the > > > set of PG-like set of routines, the

Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol

Michael Paquier wrote: > On Wed, Jul 6, 2016 at 4:18 PM, Michael Paquier > wrote: > > OK, after hacking that for a bit I have finished with option 2 and the > > set of PG-like set of routines, the use of USE_SSL in the file > > containing all the SHA functions of

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-07-20 13:59:32 -0400, Robert Haas wrote: > It's hard to believe that it's equally good to use the newest > registered snapshot (which is, I think, what you will often get from > GetActiveSnapshot()) and the oldest registered snapshot (which is what > you will get from pairingheap_first()).

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Wed, Jul 20, 2016 at 12:30 PM, Andres Freund wrote: >> And how do you obtain that? The functions that reference >> SnapshotToast are toast_delete_datum, toastrel_value_exists, and >> toast_fetch_datum, toast_fetch_datum_slice, but none of those take a >> snapshot as an

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

Andres Freund wrote: > On 2016-07-20 11:26:11 -0400, Robert Haas wrote: > > On Wed, Jul 20, 2016 at 3:39 AM, Andres Freund wrote: > > >>I think Snapshot's members whenTaken and lsn are updated/initialized > > >>only in GetSnapshotData(). So if GetSnapshotData() is not used,

Re: [HACKERS] Design for In-Core Logical Replication

On 20 July 2016 at 17:52, Rod Taylor wrote: > I think it's important for communication channels to be defined separately > from the subscriptions. > I agree and believe it will be that way. Craig is working on allowing Replication Slots to failover between nodes, to

Re: [HACKERS] Design for In-Core Logical Replication

On 20 July 2016 at 16:39, Joshua D. Drake wrote: > Logical Replication is a method of replicating data objects and their >> changes, based upon their Primary Keys (or Replication Identity). We >> > > Do we want a limitation based on Primary Key, or would it be

Re: [HACKERS] skink's test_decoding failures in 9.4 branch

Hi, On 2016-07-20 12:45:04 -0400, Tom Lane wrote: > I wrote: > > I've still had no luck reproducing it here, though. Same here so far. > Hah --- I take that back. On about the fourth or fifth trial: Interesting. > ==00:00:00:34.291 21525== Invalid read of size 1 > ==00:00:00:34.291 21525==

Re: [HACKERS] One process per session lack of sharing

On 12 July 2016 at 09:57, > wrote: We have faced with some lack of sharing resources. So in our test memory usage per session: Oracle: about 5M MSSqlServer: about 4M postgreSql: about 160М Using shared resources also has

Re: [HACKERS] Design for In-Core Logical Replication

On Wed, Jul 20, 2016 at 4:08 AM, Simon Riggs wrote: > > > And on Subscriber database: > > CREATE SUBSCRIPTION mysub WITH CONNECTION dbname=foo host=bar > user=repuser PUBLICATION mypub; > > > > The above will start the replication process which

Re: [HACKERS] skink's test_decoding failures in 9.4 branch

I wrote: > I've still had no luck reproducing it here, though. Hah --- I take that back. On about the fourth or fifth trial: ==00:00:00:34.291 21525== Invalid read of size 1 ==00:00:00:34.291 21525==at 0x4A08DEC: memcpy (mc_replace_strmem.c:882) ==00:00:00:34.291 21525==by 0x66FA54:

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-07-20 11:26:11 -0400, Robert Haas wrote: > On Wed, Jul 20, 2016 at 3:39 AM, Andres Freund wrote: > >>I think Snapshot's members whenTaken and lsn are updated/initialized > >>only in GetSnapshotData(). So if GetSnapshotData() is not used, how > >>will you expect those

[HACKERS] skink's test_decoding failures in 9.4 branch

I can't help noticing that the failure rate on skink has gone from "rare" to "100%" since 3d5b227: http://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=skink=REL9_4_STABLE I think we need to put some effort into figuring out what's up there. Also, this morning curculio showed what might be

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Wed, Jul 20, 2016 at 3:39 AM, Andres Freund wrote: >>I think Snapshot's members whenTaken and lsn are updated/initialized >>only in GetSnapshotData(). So if GetSnapshotData() is not used, how >>will you expect those fields to be updated. We need those fields to >>be

Re: [HACKERS] Design for In-Core Logical Replication

On 07/20/2016 01:08 AM, Simon Riggs wrote: Logical Replication Logical Replication is a method of replicating data objects and their changes, based upon their Primary Keys (or Replication Identity). We Do we want a limitation based on Primary Key, or would it be possible

Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol

On Fri, Jul 15, 2016 at 9:30 AM, Michael Paquier wrote: > OK, I am doing that at the end. > > And also while moving on... > > On another topic, here are some ideas to extend CREATE/ALTER ROLE to > support SCRAM password directly: > 1) protocol PASSWORD value, where

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On Wed, Jul 20, 2016 at 10:15 AM, Tom Lane wrote: > Robert Haas writes: >> Mumble. Why, exactly, was this a good idea? The upside of commit >> 45639a0525a58a2700cf46d4c934d6de78349dac is only that you do fewer >> plan invalidations, but surely that's

Re: [HACKERS] dumping database privileges broken in 9.6

* Noah Misch (n...@leadboat.com) wrote: > On Fri, Jul 15, 2016 at 03:46:17PM -0400, Stephen Frost wrote: > > * Noah Misch (n...@leadboat.com) wrote: > > > On Sat, Jul 09, 2016 at 12:55:33AM -0400, Stephen Frost wrote: > > > > * Noah Misch (n...@leadboat.com) wrote: > > > > > This PostgreSQL 9.6

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

Robert Haas writes: > Mumble. Why, exactly, was this a good idea? The upside of commit > 45639a0525a58a2700cf46d4c934d6de78349dac is only that you do fewer > plan invalidations, but surely that's not a significant benefit for > most people: user mappings don't change that

Re: [HACKERS] dumping database privileges broken in 9.6

* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Sat, Jul 16, 2016 at 4:46 AM, Stephen Frost wrote: > > Going through and doing testing now. Unfortunately, it doesn't look > > like adding in testing of tablespaces into the TAP tests would be very > > easy (the only

Re: [HACKERS] (re)start in our init scripts seems broken

Michael Paquier writes: > On Wed, Jul 20, 2016 at 11:41 AM, Tomas Vondra > wrote: >> Is there a reason why it's coded like this? I think we should use the pg_ctl >> instead or (at the very least) check the postmaster return code. Also, >>

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Tue, Jul 19, 2016 at 6:32 PM, Andres Freund wrote: > I mean the only difference between toast / plain heap table WRT > old_snapshot_threshold is that we don't use a mvcc snapshot. We use different functions and never, ever call BufferGetPage -- except for deep in the

Re: [HACKERS] sslmode=require fallback

Magnus Hagander wrote: > > I don't understand why you want to change the default. Is it for > > performance? Has it been measured? > > > > > Yes. I've run into it multiple times, but I haven't specifically measured > it. But I've had more than one situation where turning it off has >

Re: [HACKERS] Adjust recovery test file name

On Wed, Jul 20, 2016 at 5:08 AM, Alvaro Herrera wrote: > Masahiko Sawada wrote: >> Hi all, >> >> The file 006_logical_decoding_timelines.pl was removed by the commit c1543a8. >> But currently 005_***.pl and 007_***.pl exist on source tree. >> Should we change its file

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On Fri, Jul 15, 2016 at 5:25 PM, Tom Lane wrote: > I wrote: >> Etsuro Fujita writes: >>> Here is a patch for that redesign proposed by you; reverts commits >>> fbe5a3fb73102c2cfec114a67943f4474383 and >>>

Re: [HACKERS] [BUG] pg_basebackup from disconnected standby fails

On Wed, Jul 20, 2016 at 8:40 PM, Amit Kapila wrote: > On Wed, Jul 20, 2016 at 5:12 AM, Michael Paquier > wrote: >> On Tue, Jul 19, 2016 at 9:08 PM, Amit Kapila wrote: >>> On Tue, Jul 19, 2016 at 10:43 AM, Michael

Re: [HACKERS] [BUG] pg_basebackup from disconnected standby fails

On Wed, Jul 20, 2016 at 5:12 AM, Michael Paquier wrote: > On Tue, Jul 19, 2016 at 9:08 PM, Amit Kapila wrote: >> On Tue, Jul 19, 2016 at 10:43 AM, Michael Paquier >> wrote: >>> On Sat, Jul 16, 2016 at 9:20 PM, Amit

Re: [HACKERS] sslmode=require fallback

On Tue, Jul 19, 2016 at 10:57 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 7/19/16 3:32 PM, Magnus Hagander wrote: > > There are definitely cases where it's useful. I'm only arguing for > > changing the default. > > I don't understand why you want to change the default.

Re: [HACKERS] sslmode=require fallback

Iirc we changed the default to be SSL for localhost to address a particular problem. It seemed surprising at the time but it was the most effective solution. On 19 Jul 2016 21:58, "Peter Eisentraut" wrote: > On 7/19/16 3:32 PM, Magnus Hagander wrote: > > There

[HACKERS] Re: FDW handling count(*) through AnalyzeForeignTable or other constant time push-down

Hi Gabe, Did you get Aggregate Pushdown FDW plugin? Would be really helpful if you can share some insight on your investigation. Regards Anant -- View this message in context:

[HACKERS] Design for In-Core Logical Replication

At PgCon we discussed that Petr Jelinek would be working on the code for an in-core logical replication implementation, while I would work on user interface/security models. Petr has been actively working on the code and will post patch in a few weeks, as discussed and agreed. Craig Ringer is also

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On July 19, 2016 7:43:05 PM PDT, Amit Kapila wrote: >On Wed, Jul 20, 2016 at 7:57 AM, Andres Freund >wrote: >> >> >> On July 19, 2016 7:14:42 PM PDT, Amit Kapila > wrote: >>>On Wed, Jul 20, 2016 at 5:02 AM, Andres Freund