Re: [HACKERS] Error in running DBT2
Hi All, On Tue, May 13, 2014 at 9:44 PM, Peter Geoghegan wrote: > > On Tue, May 13, 2014 at 12:36 PM, Rohit Goyal wrote: > >> This pattern the above found many times. Please guide me through!!! >> > > IIRC, people have been working around this by setting > standard_conforming_strings to "off". It really ought to be fixed in a > principled way, though -- the real issue here is that dbt2 has severe > bit-rot. > > Sorry for so asking so late. I thought the error is resolved. I changed standard_conforming_strings to "off" in *postgresql.conf.* and then I run *dbt2-pgsql-build-db -w 1 script again. * But, I am still facing the same error. Please tel me what else I can do to resolve the issue. Regards, Rohit Goyal > -- > Peter Geoghegan > -- Regards, Rohit Goyal
Re: [HACKERS] Error in running DBT2
Hi All, Just adding the actual error again. I run the the *dbt2-pgsql-build-db -w 1 * Output directory of data files: current directory *Generating data files for 1 warehouse(s)...* *Generating item table data...* *BEGIN* *ERROR: invalid byte sequence for encoding "UTF8": 0xf9* *CONTEXT: COPY item, line 1* *ROLLBACK* This pattern the above found many times. Please guide me through!!! Regards, Rohit Goyal On Tue, May 20, 2014 at 9:15 AM, Rohit Goyal wrote: > Hi All, > > > > On Tue, May 13, 2014 at 9:44 PM, Peter Geoghegan wrote: > >> >> On Tue, May 13, 2014 at 12:36 PM, Rohit Goyal wrote: >> >>> This pattern the above found many times. Please guide me through!!! >>> >> >> IIRC, people have been working around this by setting >> standard_conforming_strings to "off". It really ought to be fixed in a >> principled way, though -- the real issue here is that dbt2 has severe >> bit-rot. >> >> > Sorry for so asking so late. I thought the error is resolved. > > I changed standard_conforming_strings to "off" in *postgresql.conf.* and > then I run *dbt2-pgsql-build-db -w 1 script again. * > But, I am still facing the same error. Please tel me what else I can do to > resolve the issue. > > Regards, > Rohit Goyal > >> -- >> Peter Geoghegan >> > > > > -- > Regards, > Rohit Goyal > -- Regards, Rohit Goyal
Re: [HACKERS] Error in running DBT2
On 05/20/2014 03:39 AM, Rohit Goyal wrote: Hi All, Just adding the actual error again. I run the the *dbt2-pgsql-build-db -w 1 * Output directory of data files: current directory *Generating data files for 1 warehouse(s)...* *Generating item table data...* *BEGIN* *ERROR: invalid byte sequence for encoding "UTF8": 0xf9* *CONTEXT: COPY item, line 1* *ROLLBACK* This pattern the above found many times. Please guide me through!!! Please stop posting this to postgresql-hackers. We've already told you it's the wrong forum. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in running DBT2
On Tue, May 20, 2014 at 9:57 AM, Andrew Dunstan wrote: > > On 05/20/2014 03:39 AM, Rohit Goyal wrote: > >> Hi All, >> >> Just adding the actual error again. >> >> I run the the *dbt2-pgsql-build-db -w 1 * >> >> >> Output directory of data files: current directory >> >> *Generating data files for 1 warehouse(s)...* >> *Generating item table data...* >> *BEGIN* >> *ERROR: invalid byte sequence for encoding "UTF8": 0xf9* >> *CONTEXT: COPY item, line 1* >> *ROLLBACK* >> >> >> This pattern the above found many times. Please guide me through!!! >> >> >> >> > > Please stop posting this to postgresql-hackers. We've already told you > it's the wrong forum. > > cheers > > Hi Andrew, I am so sorry, it was by mistake. :). I was not able to analyse whether the issue belong to postgresql or dbt2. Regards, Rohit > andrew >
[HACKERS] HEAD crashes with assertion and LWLOCK_STATS enabled
Hi hackers,
I found a bug that causes a crash when assertion is enabled and LWLOCK_STATS is
defined.
I've tested with Debian 7.5 (3.2.0-4-amd64) on VMware fusion 6, but this bug
seems to be platform-independent and should reproduce in other environments.
A patch to fix the bug is also attached.
## Reproduing a crash
You can reproduce a crash by this way:
git co a0841ecd2518d4505b96132b764b918ab5d21ad4
git clean -dfx
./configure --enable-cassert CFLAGS='-DLWLOCK_STATS'
make check
In my environment, the following messages appeared.
( omit... )
../../../src/test/regress/pg_regress --inputdir=.
--temp-install=./tmp_check --top-builddir=../../..--dlpath=.
--schedule=./parallel_schedule
== creating temporary installation==
== initializing database system ==
pg_regress: initdb failed
and initdb.log contained the following messages.
reating directory /tmp/pghead/src/test/regress/./tmp_check/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in
/tmp/pghead/src/test/regress/./tmp_check/data/base/1 ... PID 48239 lwlock main
142: shacq 0 exacq 1 blk 0 spindelay 0
( omit... )
PID 48247 lwlock main 33058: shacq 0 exacq 1 blk 0 spindelay 0
PID 48247 lwlock main 33005: shacq 0 exacq 48 blk 0 spindelay 0
ok
loading system objects' descriptions ... TRAP:
FailedAssertion("!(CritSectionCount == 0 || (context) == ErrorContext ||
(MyAuxProcType == CheckpointerProcess))", File: "mcxt.c", Line: 594)
Aborted (core dumped)
child process exited with exit code 134
initdb: data directory "/tmp/pghead/src/test/regress/./tmp_check/data" not
removed at user's request
## The cause of crash
The failing assertion is for prohibiting memory allocation in a critical
section, which is introduced by commit 4a170ee9 on 2014-04-04.
In my understanding, the root cause of the assertion failure is on-demand
allocation of lwlock_stats entry. For each LWLock, a lwlock_stats entry is
created at the first invocation of LWLockAcquire using MemoryContextAlloc. If
the first invocation is in a critical section, the assertion fails.
For 'initdb' case I mentioned above, WALWriteLock locking in XLogFlush function
was the problem.
I also confirmed the assertion failure on starting postgres on a correctly
initialized database. In this case, locking CheckpointerCommLock in
AbsorbFsyncRequests function was the problem.
## A solution
In order to avoid memory allocation during critical sections, lwlock_stats hash
table should be populated at the initialization of each process.
The attached patch populate lwlock_stats entries of MainLWLockArray at the end
of CreateLWLocks, InitProcess and InitAuxiliaryProcess.
With this patch, all regression tests can be passed so far, but I think this
patch is not perfect because it does not cover LWLocks outside of
MainLWLockArray. I'm not sure where is the right place to initialize
lwlock_stats entries for that locks. So I feel it needs some refinements by
you hackers.
From f96708d14ab0073abd95c463eaf8d60db42f411d Mon Sep 17 00:00:00 2001
From: Yuto Hayamizu
Date: Tue, 20 May 2014 16:19:56 +0900
Subject: [PATCH] pre-populating lwlock_stats entries
---
src/backend/storage/lmgr/lwlock.c | 21 +
src/backend/storage/lmgr/proc.c |8
src/include/storage/lwlock.h |4
3 files changed, 33 insertions(+)
diff --git a/src/backend/storage/lmgr/lwlock.c
b/src/backend/storage/lmgr/lwlock.c
index d23ac62..fc97ae0 100644
--- a/src/backend/storage/lmgr/lwlock.c
+++ b/src/backend/storage/lmgr/lwlock.c
@@ -359,8 +359,29 @@ CreateLWLocks(void)
MainLWLockTranche.array_base = MainLWLockArray;
MainLWLockTranche.array_stride = sizeof(LWLockPadded);
LWLockRegisterTranche(0, &MainLWLockTranche);
+
+#ifdef LWLOCK_STATS
+ InitializeLWLockStats();
+#endif
}
+#ifdef LWLOCK_STATS
+void
+InitializeLWLockStats(void)
+{
+ int numLocks = NumLWLocks();
+ int id;
+ LWLockPadded*lock;
+
+ if (MyProc == NULL) return;
+
+ /* Initialize all lwlock_stats entries of MainLWLockArray */
+ for (id = 0, lock = MainLWLockArray; id < numLocks; id++, lock++)
+ {
+ get_lwlock_stats_entry(&lock->lock);
+ }
+}
+#endif
/*
* LWLockAssign - assign a dynamically-allocated LWLock number
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index 266b0da..e09cbf8 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -415,6 +415,10 @@ InitProcess(void)
* the deadlock checker.
*/
InitDeadLockChecking();
+
+#ifde
Re: [HACKERS] Allowing join removals for more join types
On Mon, May 19, 2014 at 9:22 PM, Dilip kumar wrote: > On 19 May 2014 12:15 David Rowley Wrote, > > > > > > May be we can convert my above example like below à in this case we > have unique index on field a and we are limiting it by first 100 tuple > (record are already order because of index) > > > > Create table t1 (a int, b int); > > Create table t2 (a int, b int); > > Create unique index on t2(a); > > > > create view v1 as > > select x.a, y.b > > from t1 x left join (select t2.a a1, b from t2 limit 100) as y on > x.a=y.a1; > > > > select a from v1; à for this query I think left join can be removed, But > in view since non join field(b) is also projected so this cannot be > simplified there. > > > Ok I see what you mean. I guess then that if we did that then we should also support removals of join in subqueries of subqueries. e.g: select t1.* from t1 left join (select t2.uniquecol from (select t2.uniquecol from t2 limit 1000) t2 limit 100) t2 on t1.id = t2.uniquecol On my first round of thoughts on this I thought that we could keep looking into the sub queries until we find that the sub query only queries a single table or it is not a base relation. If we find one with a single table and the sub query has no distinct or group bys then I thought we could just look at the unique indexes similar to how it's done now for a direct table join. But after giving this more thought, I'm not quite sure if a lack of DISTINCT and GROUP BY clause is enough for us to permit removing the join. Would it matter if the sub query did a FOR UPDATE? I started looking at is_simple_subquery() in prepjointree.c but if all those conditions were met then the subquery would have been pulled up to a direct join anyway. I'm also now wondering if I need to do some extra tests in the existing code to ensure that the subquery would have had no side affects. For example: SELECT t1.* FROM t1 LEFT OUTER JOIN (SELECT id,some_function_that_does_something(id) FROM t2 GROUP BY id) t2 ON t1.id = t2.id; Regards David Rowley
Re: [HACKERS] Negative imact of maintenance_work_mem to GIN size
On Tue, May 20, 2014 at 4:50 AM, Alexander Korotkov wrote: > I found that sometimes larger maintenance_work_mem leads to larger GIN > index. That is quite strange. ISTM that it's related to posting lists > compression but I can't figure out how exactly it is. > It appears to be not related to posting lists compression. I did reproduce it on 9.2. create table test as (select array[(random()*1000)::int]::int[] as v from generate_series(1,1000) g); set maintenance_work_mem = '2GB'; create index test_idx1 on test using gin(v); set maintenance_work_mem = '16MB'; create index test_idx2 on test using gin(v); Schema |Name| Type | Owner |Table| Size | Description ++---+--+-+-+- public | test_idx1 | index | smagen | test| 392 MB | public | test_idx2 | index | smagen | test| 268 MB | (2 rows) The reason of it is that we filling entry tree with inserting without any special optimization. Large maintenance_work_mem gives us ascending order of insertion. Thus insertion is performed always into rightmost page leaving rest of pages half-empty. Small maintenance_work_mem gives us more random order of insertion. Such insertions makes pages about 75% filled in average. Posting trees has special optimization for this case while entry tree doesn't. -- With best regards, Alexander Korotkov.
Re: [HACKERS] buildfarm animals and 'snapshot too old'
Robert Haas writes: > On Mon, May 19, 2014 at 7:58 PM, Andrew Dunstan wrote: >> Well, the original code was put in for a reason, presumably that we were >> getting some stale data and wanted to exclude it. So I'm unwilling to throw >> it out altogether. If someone can propose a reasonable sanity check then I'm >> prepared to implement it. > While I generally agree that long-established code shouldn't be > changed for light or transient causes, I have to admit I'm pretty > skeptical about this particular instance. I can't think of any > particularly compelling reason why it's BAD for an old result to show > up. We now show the commit ID on the main page, so if you see 512abc4 > in the middle of a bunch of ef9ab5f's, you'll notice. And if you > don't notice, so what? Robert's got a point here. In my usage, the annoying thing is not animals that take a long time to report in; it's the ones that lie about the snapshot time (which is how you get "512abc4 in the middle of a bunch of ef9ab5f's"). That is an issue of incorrect system clock, not of how long it takes to do the run. I wonder if the buildfarm script could be taught to get the timestamp from an NTP server somewhere? Or at least sanity-check the system clock reading by comparing it to the newest commit timestamp in the git repo. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing join removals for more join types
David Rowley writes: > I'm also now wondering if I need to do some extra tests in the existing > code to ensure that the subquery would have had no side affects. You should probably at least refuse the optimization if the subquery's tlist contains volatile functions. Functions that return sets might be problematic too [ experiments... ] Yeah, they are. This behavior is actually a bit odd: regression=# select q1 from int8_tbl; q1 -- 123 123 4567890123456789 4567890123456789 4567890123456789 (5 rows) regression=# select q1 from int8_tbl group by 1; q1 -- 4567890123456789 123 (2 rows) regression=# select q1,unnest(array[1,2]) as u from int8_tbl; q1| u --+--- 123 | 1 123 | 2 123 | 1 123 | 2 4567890123456789 | 1 4567890123456789 | 2 4567890123456789 | 1 4567890123456789 | 2 4567890123456789 | 1 4567890123456789 | 2 (10 rows) regression=# select q1,unnest(array[1,2]) as u from int8_tbl group by 1; q1| u --+--- 4567890123456789 | 1 4567890123456789 | 2 123 | 1 123 | 2 (4 rows) EXPLAIN shows that the reason the last case behaves like that is that the SRF is expanded *after* the grouping step. I'm not entirely sure if that's a bug --- given the lack of complaints, perhaps not. But it shows you can't apply this optimization without changing the existing behavior. I doubt you should drop a subquery containing FOR UPDATE, either. That's a side effect, just as much as a volatile function would be. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Priority table or Cache table
On Mon, Mar 17, 2014 at 1:16 PM, Haribabu Kommi wrote: > On Fri, Feb 21, 2014 at 12:02 PM, Haribabu Kommi > wrote: >> On Thu, Feb 20, 2014 at 10:06 PM, Ashutosh Bapat >> wrote: >>> >>> On Thu, Feb 20, 2014 at 10:23 AM, Haribabu Kommi >>> wrote: On Thu, Feb 20, 2014 at 2:26 PM, Amit Kapila wrote: > > On Thu, Feb 20, 2014 at 6:24 AM, Haribabu Kommi > wrote: > > On Thu, Feb 20, 2014 at 11:38 AM, Tom Lane wrote: > >> > I want to propose a new feature called "priority table" or "cache > >> > table". > >> > This is same as regular table except the pages of these tables are > >> > having > >> > high priority than normal tables. These tables are very useful, > >> > where a > >> > faster query processing on some particular tables is expected. > >> > >> Why exactly does the existing LRU behavior of shared buffers not do > >> what you need? > > > > > > Lets assume a database having 3 tables, which are accessed regularly. > > The > > user is expecting a faster query results on one table. > > Because of LRU behavior which is not happening some times. > > I Implemented a proof of concept patch to see whether the buffer pool > split can improve the performance or not. > > Summary of the changes: > 1. The priority buffers are allocated as continuous to the shared buffers. > 2. Added new reloption parameter called "buffer_pool" to specify the > buffer_pool user wants the table to use. I'm not sure if storing the information of "priority table" into database is good because this means that it's replicated to the standby and the same table will be treated with high priority even in the standby server. I can imagine some users want to set different tables as high priority ones in master and standby. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] buildfarm: strange OOM failures on markhor (running CLOBBER_CACHE_RECURSIVELY)
On 2014-05-19 13:45:15 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2014-05-19 11:25:04 -0400, Tom Lane wrote: > >> No, we'd have two independent entries, each with its own correct refcount. > >> When the refcount on the no-longer-linked-in-the-hashtable entry goes to > >> zero, it'd be leaked, same as it's always been. (The refcount presumably > >> corresponds to someone holding a direct pointer to the Relation struct, > >> which is what they'd use to decrement the refcount.) > > > The problem is that only one of these entries will get properly handled > > by cache invalidation. > > I don't think that's a problem; if the old entry is still referenced, > presumably the holder of the reference is also holding a lock sufficient > to prevent any problematic schema changes. Except if it's during ddl itself... But we'd probably seen problems by now if there aactually probem. > > I thought about it for a while and I wonder if that's necessarily > > correct. If somebody registers a relcache invalidation callback that > > could happen when invalidations are processed somewhere while rebuilding > > a entry? > > Cache invalidation callbacks shouldn't do anything that would involve > opening non-system relations, IMO. I think that's unneccessarily restrictive. There's good reasons to build caches ontop the relcache that also have to visit user defined relations. Imagine the configuration tables of a replication solution for example. Since there's no builtin, integrated, way to add data to relcache entries that's pretty much the only choice. It's probably best to avoid these problems by simply setting an ->invalid flag or something, but I can imagine cases where that's not the best strategy. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] buildfarm animals and 'snapshot too old'
On 05/20/2014 07:09 AM, Tom Lane wrote: Robert Haas writes: On Mon, May 19, 2014 at 7:58 PM, Andrew Dunstan wrote: Well, the original code was put in for a reason, presumably that we were getting some stale data and wanted to exclude it. So I'm unwilling to throw it out altogether. If someone can propose a reasonable sanity check then I'm prepared to implement it. While I generally agree that long-established code shouldn't be changed for light or transient causes, I have to admit I'm pretty skeptical about this particular instance. I can't think of any particularly compelling reason why it's BAD for an old result to show up. We now show the commit ID on the main page, so if you see 512abc4 in the middle of a bunch of ef9ab5f's, you'll notice. And if you don't notice, so what? Robert's got a point here. In my usage, the annoying thing is not animals that take a long time to report in; it's the ones that lie about the snapshot time (which is how you get "512abc4 in the middle of a bunch of ef9ab5f's"). That is an issue of incorrect system clock, not of how long it takes to do the run. I wonder if the buildfarm script could be taught to get the timestamp from an NTP server somewhere? Or at least sanity-check the system clock reading by comparing it to the newest commit timestamp in the git repo. Showing the commit id is a relatively recent phenomenon, dating back to July 2013. I agree with Robert that it might obsolete this check, so I have disabled it for now. I have also disabled the other timestamp check, on the time the client actually took the snapshot (as opposed to the time of the last commit in the snapshot) for the CLOBBER_CACHE_RECURSIVELY case. Regarding clock skew, I think we can do better then what you suggest. The web transaction code in the client adds its own timestamp just before running the web transaction. It would be quite reasonable to reject reports from machines with skewed clocks based on this value. I'm not sure what a reasonable skew might be. Somewhere in the range of 5 to 15 minutes seems reasonable. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] buildfarm animals and 'snapshot too old'
Andrew Dunstan writes: > On 05/20/2014 07:09 AM, Tom Lane wrote: >> Robert's got a point here. In my usage, the annoying thing is not animals >> that take a long time to report in; it's the ones that lie about the >> snapshot time (which is how you get "512abc4 in the middle of a bunch of >> ef9ab5f's"). That is an issue of incorrect system clock, not of how long >> it takes to do the run. I wonder if the buildfarm script could be taught >> to get the timestamp from an NTP server somewhere? Or at least >> sanity-check the system clock reading by comparing it to the newest commit >> timestamp in the git repo. > Regarding clock skew, I think we can do better then what you suggest. > The web transaction code in the client adds its own timestamp just > before running the web transaction. It would be quite reasonable to > reject reports from machines with skewed clocks based on this value. I'm > not sure what a reasonable skew might be. Somewhere in the range of 5 to > 15 minutes seems reasonable. Rather than reject, why not take the result and adjust the claimed start timestamp by the difference between the web transaction timestamp and the buildfarm server's time? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Buffer manager scalability and correlated reference period
On Fri, May 16, 2014 at 1:22 PM, Peter Geoghegan wrote: > > I have performed a new benchmark related to my ongoing experimentation > around caching and buffer manager scalability. The benchmark tests a > minor refinement of the prototype patch previously posted [1]. The > patch itself is still very much a prototype, and does not > significantly differ from what I originally posted. The big > difference is usage_count starts at 6, and saturates at 30, plus I've > tried to reduce the impact of the prior prototype's gettimeofday() > calls by using clock_gettime() + CLOCK_MONOTONIC_COARSE. I previously > posted some numbers for a patch with just the former change. > > I effectively disabled the background writer entirely here, since it > never helps. These are unlogged tables, so as to not have the outcome > obscured by checkpoint spikes during the sync phase that are more or > less inevitable here (I believe this is particularly true given the > hardware I'm using). Multiple client counts are tested, giving some > indication of the impact on scalability. The same gains previously > demonstrated in both transaction throughput and latency are once again > clearly in evidence. > > I should emphasize that although I've talked a lot about LRU-K and > other more sophisticated algorithms, this proof of concept still only > adds a correlated reference period (while allowing usage_count to span > a larger range). I have yet to come up with something really > interesting, such as a patch that makes an inference about the > frequency of access of a page based on the recency of its penultimate > access (that is, a scheme that is similar to LRU-K, a scheme known to > be used in other systems [2] and thought to be widely influential). One point which I observed while reading the paper mentioned by you '[2]' is that, they are telling to have replacement policy based on page type (index-root page, heap page, etc) which seems to be relevant considering that proportion of read statements using index scan is quite higher. Another thing that caught my eye while reading LRU-K paper is to give consideration for correlated reference pairs [1] in usage_count maintenance (increment/decrement) , basically access patterns for Intra-Transaction and Intra-Process (there is one another mentioned in paper as Transaction-Retry which I am not if it is much relevant). [1]: Definitions copied from LRU-K page, just for ease of reference (a) Intra-Transaction. A transaction accesses a page, then accesses the same page again before cmnrnitting. This is likely to happen with certain update transactions, first reading a row and later updating a value in the row. (b) Transaction-Retry. A transaction accesses a page, then aborts and is retried, and the retried transaction accesses the page again for the same purpose. (c) Intra-Process. A transaction references a page, then commits, and the next transaction by the same process accesses the page again. This pattern of access commonly arises in batch update applications, which update 10 records in sequence, commit, then start again by referencing the next record on the same page. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
[HACKERS] I thought we were changing the name of recvlogical?
I can't find the thread now, but I'm pretty sure that we decided to change the name of pg_recvlogical, because its inconsistent with other client utils? No? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I thought we were changing the name of recvlogical?
On 05/20/2014 08:48 AM, Josh Berkus wrote: I can't find the thread now, but I'm pretty sure that we decided to change the name of pg_recvlogical, because its inconsistent with other client utils? No? This thread, perhaps?? http://www.postgresql.org/message-id/20130923084634.ga15...@awork2.anarazel.de Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I thought we were changing the name of recvlogical?
Josh Berkus wrote: > I can't find the thread now, but I'm pretty sure that we decided to > change the name of pg_recvlogical, because its inconsistent with other > client utils? No? There are others who think this has already happened. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I thought we were changing the name of recvlogical?
On 05/20/2014 12:07 PM, Alvaro Herrera wrote: > Josh Berkus wrote: >> I can't find the thread now, but I'm pretty sure that we decided to >> change the name of pg_recvlogical, because its inconsistent with other >> client utils? No? > > There are others who think this has already happened. > Well, I just compiled beta1, and it's still called recvlogical. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I thought we were changing the name of recvlogical?
On 2014-05-20 12:33:20 -0400, Josh Berkus wrote: > On 05/20/2014 12:07 PM, Alvaro Herrera wrote: > > Josh Berkus wrote: > >> I can't find the thread now, but I'm pretty sure that we decided to > >> change the name of pg_recvlogical, because its inconsistent with other > >> client utils? No? > > > > There are others who think this has already happened. > > > > Well, I just compiled beta1, and it's still called recvlogical. It was named pg_receivelogical initially. Then people argued that that's too long and voted for pg_recvlogical. I renamed it even though I didn't particularly like the new name. I fail to see the point in renaming it again. And I haven't seen any concensus for doing so either. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I thought we were changing the name of recvlogical?
On 05/20/2014 12:38 PM, Andres Freund wrote: > On 2014-05-20 12:33:20 -0400, Josh Berkus wrote: >> On 05/20/2014 12:07 PM, Alvaro Herrera wrote: >>> Josh Berkus wrote: I can't find the thread now, but I'm pretty sure that we decided to change the name of pg_recvlogical, because its inconsistent with other client utils? No? >>> >>> There are others who think this has already happened. >>> >> >> Well, I just compiled beta1, and it's still called recvlogical. > > It was named pg_receivelogical initially. Then people argued that that's > too long and voted for pg_recvlogical. I renamed it even though I didn't > particularly like the new name. > I fail to see the point in renaming it again. And I haven't seen any > concensus for doing so either. Ah, I see. There was a name change, and I lost the argument. Got it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] rangetypes spgist questions/refactoring
I am trying to understand the rangetypes spgist code and its interaction
with empty ranges. (Slightly embarrassing, because I reviewed the code.)
I see an old email here:
http://www.postgresql.org/message-id/50145a9c.7080...@enterprisedb.com
But still don't have a clear picture.
What I don't understand is:
1. Under what conditions might a tuple have no prefix (centroid), yet
also *not* be allTheSame?
2. Why would any tuple have 2 nodes? If there are some non-empty ranges,
why not make a centroid and have 4 or 5 nodes?
I added a bunch of assertions that seem reasonable to me based on my
understanding of the structure, and I attached them as a patch. They all
pass regression, which has a non-trivial set of input ranges, so there
is a reasonable chance the assertions are generally true. But if they
are true, some refactoring is in order.
It seems like the structure should be something like:
* Empty and non-empty ranges are only mixed in the root (level == 0).
* If a tuple has any non-empty ranges, there's a prefix (centroid).
AllTheSame may or may not be set (ordinarily not).
* If a tuple has only empty ranges, there's no prefix, and allTheSame is
set.
* The root tuple may have 5 nodes if there are any non-empty ranges,
otherwise 1 node (and allTheSame is set).
* Inner tuples may have either:
- one node if it contains only empty ranges, in which case it is
allTheSame, and must be in the 5th node (quadrant) of the root node, and
be at level 1
- four nodes if there are any non-empty ranges, in which case it has
*no* empty ranges
Note: I am using "allTheSame" in the logical sense; perhaps the flag is
an internal optimization that we can't rely on.
Regards,
Jeff Davis
*** a/src/backend/utils/adt/rangetypes_spgist.c
--- b/src/backend/utils/adt/rangetypes_spgist.c
***
*** 104,109 getQuadrant(TypeCacheEntry *typcache, RangeType *centroid, RangeType *tst)
--- 104,112
range_deserialize(typcache, centroid, ¢roidLower, ¢roidUpper,
¢roidEmpty);
+
+ Assert(!centroidEmpty);
+
range_deserialize(typcache, tst, &lower, &upper, &empty);
if (empty)
***
*** 138,143 spg_range_quad_choose(PG_FUNCTION_ARGS)
--- 141,158
int16 quadrant;
TypeCacheEntry *typcache;
+ /* if there is no prefix, they must be all the same */
+ Assert(in->hasPrefix || in->allTheSame);
+ /*
+ * If there are two nodes, it must either be the root or at level 1, and
+ * there must be no prefix.
+ */
+ Assert(in->nNodes != 2 ||
+ (!in->hasPrefix && (in->level == 0 || in->level == 1)));
+ /* if there are 4 or 5 nodes, there must be a prefix */
+ Assert(in->nNodes != 4 || in->hasPrefix);
+ Assert(in->nNodes != 5 || (in->hasPrefix && in->level == 0));
+
if (in->allTheSame)
{
out->resultType = spgMatchNode;
***
*** 156,161 spg_range_quad_choose(PG_FUNCTION_ARGS)
--- 171,178
*/
if (!in->hasPrefix)
{
+ Assert(false);
+
out->resultType = spgMatchNode;
if (RangeIsEmpty(inRange))
out->result.matchNode.nodeN = 0;
***
*** 232,237 spg_range_quad_picksplit(PG_FUNCTION_ARGS)
--- 249,261
nonEmptyCount = j;
/*
+ * Only the root mixes empty and non-empty tuples.
+ */
+ Assert(nonEmptyCount == 0 ||
+ nonEmptyCount == in->nTuples ||
+ in->level == 0);
+
+ /*
* All the ranges are empty. The best we can do is to construct an inner
* node with no centroid, and put all ranges into node 0. If non-empty
* ranges are added later, they will be routed to node 1.
***
*** 315,320 spg_range_quad_inner_consistent(PG_FUNCTION_ARGS)
--- 339,356
*/
bool needPrevious = false;
+ /* if there is no prefix, they must be all the same */
+ Assert(in->hasPrefix || in->allTheSame);
+ /*
+ * If there are two nodes, it must either be the root or at level 1, and
+ * there must be no prefix.
+ */
+ Assert(in->nNodes != 2 ||
+ (!in->hasPrefix && (in->level == 0 || in->level == 1)));
+ /* if there are 4 or 5 nodes, there must be a prefix */
+ Assert(in->nNodes != 4 || in->hasPrefix);
+ Assert(in->nNodes != 5 || (in->hasPrefix && in->level == 0));
+
if (in->allTheSame)
{
/* Report that all nodes should be visited */
***
*** 327,332 spg_range_quad_inner_consistent(PG_FUNCTION_ARGS)
--- 363,370
if (!in->hasPrefix)
{
+ Assert(false);
+
/*
* No centroid on this inner node. Such a node has two child nodes,
* the first for empty ranges, and the second for non-empty ones.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I thought we were changing the name of recvlogical?
Josh Berkus wrote: > On 05/20/2014 12:38 PM, Andres Freund wrote: > > On 2014-05-20 12:33:20 -0400, Josh Berkus wrote: > >> On 05/20/2014 12:07 PM, Alvaro Herrera wrote: > >>> Josh Berkus wrote: > I can't find the thread now, but I'm pretty sure that we decided to > change the name of pg_recvlogical, because its inconsistent with other > client utils? No? > >>> > >>> There are others who think this has already happened. > >> > >> Well, I just compiled beta1, and it's still called recvlogical. > > > > It was named pg_receivelogical initially. Then people argued that that's > > too long and voted for pg_recvlogical. I renamed it even though I didn't > > particularly like the new name. > > I fail to see the point in renaming it again. And I haven't seen any > > concensus for doing so either. > > Ah, I see. There was a name change, and I lost the argument. Got it. Yes, I was quoting Douglas Adams. Sorry it was obscure: "There are some who believe that if the universe is ever completely understood, it will instantly disappear and be replaced by something even more incomprehensible. There are others who think this has already happened..." -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 release notes
On Mon, May 19, 2014 at 11:30:48AM -0400, David Johnston wrote: > On Mon, May 19, 2014 at 10:23 AM, Bruce Momjian wrote: > > On Thu, May 15, 2014 at 06:08:47PM -0700, David G Johnston wrote: > > Some errors and suggestions - my apologizes for the format as I do not > have > > a proper patching routine setup. > > > > Sorry, let me address some items I skipped on your list: > > > IIUC: Logical decoding allows for streaming of statement-scoped database > > changes. > > I think Logical decoding does more than statement-scoped database > changes, e.g. it can enable multi-master without triggers. I am > hesitant to mention specific items in the release notes for that reason. > > > Yeah, probably need to look at this as a bigger unit of work and better > understand it first. But > "to be optionally streamed in a configurable format" seems too vague. Yes, it seems vague to me too, but that's the text the features author proposed. > > IIUC: Remove line length restrictions from pgbench. > > > Uh, there is a specific place we removed the ne length restriction in > pg_bench. > > > I simply interpreted: > > Allow pgbench to process script files of any line length (Sawada Masahiko) > > The previous line limit was BUFSIZ. > > "script files of any line length" just doesn't sound right to me; and if my > re-wording is wrong then it also does not actually communicate what was > changed > very well. > Agreed. Here is the new text: Remove line length limit for pgbench scripts > > style: add comma -> Previously[,] empty arrays were returned (occurs > > frequently but is minor) > > Thanks for the comma adjustments --- I can't decide on that sometimes. > > > Not a grammar expert by any means but there are generally two uses of > "previously". Thanks for the tips. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rangetypes spgist questions/refactoring
On Tue, 2014-05-20 at 09:52 -0700, Jeff Davis wrote: > 2. Why would any tuple have 2 nodes? If there are some non-empty ranges, > why not make a centroid and have 4 or 5 nodes? This is slightly more complicated than I thought, because we need to do something about the root node if a bunch of empty ranges are inserted first. SpgSplitTuple seems to offer a way to handle the first non-empty range inserted. Unfortunately, this limitation seems to kill that idea: "This new prefix value must be sufficiently less restrictive than the original to accept the new value to be indexed, and it should be no longer than the original prefix." because there's no good way to know how large the root's prefix might eventually be. So we might be better off (not a proposal; this would break upgrade) saying that the root always has two nodes: * node0 points to all empty ranges, which all live at level 1 and have allTheSame set. * node1 points to all non-empty ranges, and every tuple in that subtree has a prefix (centroid) and 4 nodes (one for each quadrant) I am starting to see the current implementation as an optimization this idea where the root can also have a centroid if you can find one, which can save an extra level in the tree search. If my analysis is correct so far, and the assertions are correct, my proposal is something like: * remove dead code * refactor to make the invariants a little more clear * make the special case of the root tuple more clear * improve comments describing tree structure and add assertions I think this can be done without breaking upgrade compatibility, because I think the structure already satisfies the invariants I mentioned in the other email (aside from the special case of a root tuple with two nodes and no prefix). That being said, it's a little scary to refactor indexing code while trying to keep it upgrade-compatible. Thoughts? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] jsonb failed assertions
Hello
I created relative large (about 200K lines) simple table (one jsonb column):
It works, but I got a errors:
TRAP: FailedAssertion("!(va.type == jbvArray || va.type == jbvObject)",
File: "jsonb_util.c", Line: 208)
LOG: server process (PID 3851) was terminated by signal 6: Aborted
DETAIL: Failed process was running: autovacuum: ANALYZE public.t4
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because ano
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because ano
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2014-05-20 20:07:32
CEST
LOG: database system was not properly shut down; automatic recovery in
progress
Data are based on opened citylots.json (avaiable on net by google)
postgres=# \lo_import ~/citylots.json
lo_import 16510
CREATE OR REPLACE FUNCTION public.bytea_to_text(bytea)
RETURNS text
LANGUAGE sql
AS $function$
SELECT convert_from($1, current_setting('server_encoding'))
$function$
postgres=# insert into xx select bytea_to_text(lo_get(16510));
INSERT 0 1
create table t1 (data json);
insert into t1 select a::json from xx;
postgres=# create table t3(data json);
CREATE TABLE
postgres=# insert into t3 select
json_array_elements(json_object_field(data, 'features')) from t1;
INSERT 0 206560
postgres=# insert into t4 select data::jsonb from t3;
INSERT 0 206560
postgres=# select * from t4 limit 1;
data
---
{"type": "Feature", "geometry": {"type": "Polygon", "coordinates":
[[[-122.422003528252475, 37.808480096967251, 0.0],.
. [-122.422076013325281, 37.808835019815085, 0.0], [-122.421102174348633,
37.808803534992904, 0.0], [-122.421062569067.
.274, 37.808601056818148, 0.0], [-122.422003528252475, 37.808480096967251,
0.0]]]}, "properties": {"TO_ST": "0", "BLKL.
.OT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001",
"ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM":.
. "0001", "MAPBLKLOT": "0001001"}}
(1 row)
postgres=# analyze t4;
The connection to the server was lost. Attempting reset: Failed
postgres=# select version();
version
-
PostgreSQL 9.4beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.8.2 20131212 (Red Hat 4.8.2-7), 64-bit
(1 row)
Program received signal SIGABRT, Aborted.
0x00382fc35c39 in raise () from /lib64/libc.so.6
(gdb) bt
#0 0x00382fc35c39 in raise () from /lib64/libc.so.6
#1 0x00382fc37348 in abort () from /lib64/libc.so.6
#2 0x0078d8d7 in ExceptionalCondition (
conditionName=conditionName@entry=0x91daa0 "!(va.type == jbvArray ||
va.type == jbvObject)",
errorType=errorType@entry=0x7c62bc "FailedAssertion",
fileName=fileName@entry=0x91d5db "jsonb_util.c",
lineNumber=lineNumber@entry=208) at assert.c:54
#3 0x00708815 in compareJsonbContainers (a=a@entry=0x7fd8371b7cbc,
b=b@entry=0x11537b4)
at jsonb_util.c:208
#4 0x00706cc3 in jsonb_cmp (fcinfo=0x11aad18) at jsonb_op.c:244
#5 0x007b32f9 in comparison_shim (x=, y=, ssup=)
at sortsupport.c:53
#6 0x00555893 in ApplySortComparator (isNull1=0 '\000', isNull2=0
'\000', ssup=0x7fff7aff12d0,
datum2=, datum1=) at
../../../src/include/utils/sortsupport.h:143
#7 compare_scalars (a=, b=,
arg=0x7fff7aff12c0) at analyze.c:2784
#8 0x007c4625 in qsort_arg (a=a@entry=0x7fd834d3e048, n=, n@entry=27648, es=es@entry=16,
cmp=cmp@entry=0x555870 , arg=arg@entry=0x7fff7aff12c0)
at qsort_arg.c:156
#9 0x00554c21 in compute_scalar_stats (stats=0x11488f8,
fetchfunc=, samplerows=3,
totalrows=206560) at analyze.c:2366
#10 0x00556e1e in do_analyze_rel (onerel=onerel@entry=0x7fd8375c8950,
acquirefunc=,
relpages=18392, inh=inh@entry=0 '\000', elevel=elevel@entry=13,
vacstmt=0x10e6bf8, vacstmt=0x10e6bf8)
at analyze.c:528
#11 0x00557bc1 in analyze_rel (relid=relid@entry=22085,
vacstmt=vacstmt@entry=0x10e6bf8,
bstrategy=) at analyze.c:268
#12 0x005a9bf4 in vacuum (vacstmt=vacstmt@entry=0x10e6bf8,
relid=relid@entry=0,
do_toast=do_toast@entry=1 '\001', bstrategy=,
bstrategy@entry=0x0,
for_wraparound=for_wraparound@entry=0 '\000',
isTopLevel=isTopLevel@entry=1 '\001') at vacuum.c:251
#13 0x006b1b9a in standard_ProcessUtility (parsetree=0x10e6bf8,
queryString=,
context=, params=0x0, dest=
Re: [HACKERS] jsonb failed assertions
On Tue, May 20, 2014 at 11:23 AM, Pavel Stehule wrote:
> TRAP: FailedAssertion("!(va.type == jbvArray || va.type == jbvObject)",
> File: "jsonb_util.c", Line: 208)
So, what type is va.type, if not one of those two?
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb failed assertions
On Tue, May 20, 2014 at 11:40 AM, Peter Geoghegan wrote: > > So, what type is va.type, if not one of those two? You should be able to figure out which pair of JSON documents are being compared by printing JsonbIterator.dataProper within GDB (that is, you should do so for both ita and itb within compareJsonbContainers()). That might allow you to produce a simple test-case. I am in the airport right now, about to fly to pgCon, and so may not have a chance to look at this properly for a day or two. Thanks. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb failed assertions
2014-05-20 20:40 GMT+02:00 Peter Geoghegan :
> On Tue, May 20, 2014 at 11:23 AM, Pavel Stehule
> wrote:
> > TRAP: FailedAssertion("!(va.type == jbvArray || va.type == jbvObject)",
> > File: "jsonb_util.c", Line: 208)
>
> So, what type is va.type, if not one of those two?
>
va.type is zero .. jbvNull
Regards
Pavel
>
> --
> Peter Geoghegan
>
[HACKERS] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3
Hi all, I'm trying to pg_upgrade an 8.4.21 to 9.3.4. The is on Debian 7--both versions were installed from apt.postgresql.org and are encoding "UTF8" and locale "C". Here's the error: /usr/lib/postgresql/9.3/bin/pg_upgrade \ -b /usr/lib/postgresql/8.4/bin/ \ -B /usr/lib/postgresql/9.3/bin/ \ -d /var/lib/postgresql/8.4/main/ \ -D /var/lib/postgresql/9.3/main/ \ -p5433 \ -P5432 \ -u postgres \ -o "-c config_file=/etc/postgresql/8.4/main/postgresql.conf" \ -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" Performing Consistency Checks - Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system OID user data typesok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects warning Your installation contains large objects. The new database has an additional large object permission table. After upgrading, you will be given a command to populate the pg_largeobject permission table with default permissions. Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is a superuser ok Checking for prepared transactions ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade -- Analyzing all rows in the new cluster ok Freezing all rows on the new clusterok Deleting files from new pg_clog ok Copying old pg_clog to new server ok Setting next transaction ID for new cluster ok Setting oldest multixact ID on new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Restoring global objects in the new cluster ok Adding support functions to new cluster ok Restoring database schemas in the new cluster ok Removing support functions from new cluster ok Copying user relation files /var/lib/postgresql/8.4/main/base/4275487/4278965 Mismatch of relation OID in database "FNBooking": old OID 4279499, new OID 19792 Failure, exiting On 8.4.21, here's that OID: postgres=# \c "FNBooking" psql (9.3.4, server 8.4.21) You are now connected to database "FNBooking" as user "postgres". FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 4279499; relname| relfilenode | relkind ---+-+- abandone_conv | 4279499 | r (1 row) and on 9.3.4 it is the same: postgres@vdev1commandprompt2:~$ psql "FNBooking" psql (9.3.4) Type "help" for help. FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 4279499; relname| relfilenode | relkind ---+-+- abandone_conv | 4279499 | r (1 row) On 8.4.21, the new OID doesn't exist: FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 19792; relname | relfilenode | relkind -+-+- (0 rows) and on 9.3.4 it is this: FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 19792; relname | relfilenode | relkind --+-+- pg_toast_4279527 | 19792 | t (1 row) Just to check, I did a pg_dump of the 8.4.21 FNBooking database and it restored with psql to 9.3.4 with no issues but the overall migration will really be too big to go this route. Any ideas? Thanks! Jeff Ross -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb failed assertions
On Tue, May 20, 2014 at 12:03 PM, Pavel Stehule wrote: > va.type is zero .. jbvNull Thanks...any luck with identifying the two JSON documents? -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb failed assertions
NOTICE: a:>> {"type": "Feature", "geometry": null, "properties": {"TO_ST":
null, "BLKLOT": "1245063", "STREET": null, "FROM_ST": null, "LOT_NUM":
"063", "ST_TYPE": null, "ODD_EVEN": null, "BLOCK_NUM": "1245", "MAPBLKLOT":
"1245061"}}<<<
NOTICE: b:>> {"type": "Feature", "geometry": {"type": "Polygon",
"coordinates": [[[-122.476849622729347, 37.784637268897804, 0.0],
[-122.47693599079787, 37.784633351359254, 0.0], [-122.477005086381169,
37.784630217263818, 0.0], [-122.477010255706205, 37.784701504178585, 0.0],
[-122.476590928382066, 37.784720524837788, 0.0], [-122.476585758323125,
37.784649237923851, 0.0], [-122.476849622729347, 37.784637268897804,
0.0]]]}, "properties": {"TO_ST": null, "BLKLOT": "1377060", "STREET": null,
"FROM_ST": null, "LOT_NUM": "060", "ST_TYPE": null, "ODD_EVEN": null,
"BLOCK_NUM": "1377", "MAPBLKLOT": "1377060"}}<<<
Regards
Pavel
2014-05-20 21:23 GMT+02:00 Peter Geoghegan :
> On Tue, May 20, 2014 at 12:03 PM, Pavel Stehule
> wrote:
> > va.type is zero .. jbvNull
>
>
> Thanks...any luck with identifying the two JSON documents?
>
> --
> Peter Geoghegan
>
Re: [HACKERS] jsonb failed assertions
On Tue, May 20, 2014 at 12:38 PM, Pavel Stehule wrote:
> NOTICE: a:>> {"type": "Feature", "geometry": null, "properties": {"TO_ST":
> null, "BLKLOT": "1245063", "STREET": null, "FROM_ST": null, "LOT_NUM":
> "063", "ST_TYPE": null, "ODD_EVEN": null, "BLOCK_NUM": "1245", "MAPBLKLOT":
> "1245061"}}<<<
> NOTICE: b:>> {"type": "Feature", "geometry": {"type": "Polygon",
> "coordinates": [[[-122.476849622729347, 37.784637268897804, 0.0],
> [-122.47693599079787, 37.784633351359254, 0.0], [-122.477005086381169,
> 37.784630217263818, 0.0], [-122.477010255706205, 37.784701504178585, 0.0],
> [-122.476590928382066, 37.784720524837788, 0.0], [-122.476585758323125,
> 37.784649237923851, 0.0], [-122.476849622729347, 37.784637268897804,
> 0.0]]]}, "properties": {"TO_ST": null, "BLKLOT": "1377060", "STREET": null,
> "FROM_ST": null, "LOT_NUM": "060", "ST_TYPE": null, "ODD_EVEN": null,
> "BLOCK_NUM": "1377", "MAPBLKLOT": "1377060"}}<<<
I cannot immediately reproduce the problem.
Is this the original JSON? Can you show the psql output from a query
with a predicate that returns both jsonb datums?
Thanks
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] buildfarm animals and 'snapshot too old'
On 21/05/14 01:42, Tom Lane wrote: Andrew Dunstan writes: On 05/20/2014 07:09 AM, Tom Lane wrote: Robert's got a point here. In my usage, the annoying thing is not animals that take a long time to report in; it's the ones that lie about the snapshot time (which is how you get "512abc4 in the middle of a bunch of ef9ab5f's"). That is an issue of incorrect system clock, not of how long it takes to do the run. I wonder if the buildfarm script could be taught to get the timestamp from an NTP server somewhere? Or at least sanity-check the system clock reading by comparing it to the newest commit timestamp in the git repo. Regarding clock skew, I think we can do better then what you suggest. The web transaction code in the client adds its own timestamp just before running the web transaction. It would be quite reasonable to reject reports from machines with skewed clocks based on this value. I'm not sure what a reasonable skew might be. Somewhere in the range of 5 to 15 minutes seems reasonable. Rather than reject, why not take the result and adjust the claimed start timestamp by the difference between the web transaction timestamp and the buildfarm server's time? regards, tom lane I think, that if possible, any such adjustment should be noted along with the original time, so that: 1. the timing issue can be remedied 2. it is possible to link the output to any messages in the machines log etc. Cheers, Gavin
Re: [HACKERS] jsonb failed assertions
2014-05-20 21:45 GMT+02:00 Peter Geoghegan :
> On Tue, May 20, 2014 at 12:38 PM, Pavel Stehule
> wrote:
> > NOTICE: a:>> {"type": "Feature", "geometry": null, "properties":
> {"TO_ST":
> > null, "BLKLOT": "1245063", "STREET": null, "FROM_ST": null, "LOT_NUM":
> > "063", "ST_TYPE": null, "ODD_EVEN": null, "BLOCK_NUM": "1245",
> "MAPBLKLOT":
> > "1245061"}}<<<
> > NOTICE: b:>> {"type": "Feature", "geometry": {"type": "Polygon",
> > "coordinates": [[[-122.476849622729347, 37.784637268897804, 0.0],
> > [-122.47693599079787, 37.784633351359254, 0.0], [-122.477005086381169,
> > 37.784630217263818, 0.0], [-122.477010255706205, 37.784701504178585,
> 0.0],
> > [-122.476590928382066, 37.784720524837788, 0.0], [-122.476585758323125,
> > 37.784649237923851, 0.0], [-122.476849622729347, 37.784637268897804,
> > 0.0]]]}, "properties": {"TO_ST": null, "BLKLOT": "1377060", "STREET":
> null,
> > "FROM_ST": null, "LOT_NUM": "060", "ST_TYPE": null, "ODD_EVEN": null,
> > "BLOCK_NUM": "1377", "MAPBLKLOT": "1377060"}}<<<
>
> I cannot immediately reproduce the problem.
>
> Is this the original JSON? Can you show the psql output from a query
> with a predicate that returns both jsonb datums?
>
This json is printed by JsonToCString
Datum
jsonb_cmp(PG_FUNCTION_ARGS)
{
Jsonb *jba = PG_GETARG_JSONB(0);
Jsonb *jbb = PG_GETARG_JSONB(1);
int res;
char *jba_str = JsonbToCString(NULL, &jba->root, VARSIZE(jba));
char *jbb_str = JsonbToCString(NULL, &jbb->root, VARSIZE(jbb));
elog(NOTICE, "a:>> %s<<<", jba_str);
elog(NOTICE, "b:>> %s<<<", jbb_str);
pfree(jba_str);
pfree(jbb_str);
res = compareJsonbContainers(&jba->root, &jbb->root);
PG_FREE_IF_COPY(jba, 0);
PG_FREE_IF_COPY(jbb, 1);
PG_RETURN_INT32(res);
}
postgres=# select * from t3 where data->'properties'->>'MAPBLKLOT' =
'1377060' and data->'properties'->>'LOT_NUM' = '060';;
data
-
{ "type": "Feature", "properties": { "MAPBLKLOT": "1377060", "BLKLOT":
"1377060", "BLOCK_NUM": "1377", "LOT_NUM": "060", "FROM_ST":.
. null, "TO_ST": null, "STREET": null, "ST_TYPE": null, "ODD_EVEN": null },
"geometry": { "type": "Polygon", "coordinates": [ [ [ -1.
.22.476849622729347, 37.784637268897804, 0.0 ], [ -122.47693599079787,
37.784633351359254, 0.0 ], [ -122.477005086381169, 37.7846302.
.17263818, 0.0 ], [ -122.477010255706205, 37.784701504178585, 0.0 ], [
-122.476590928382066, 37.784720524837788, 0.0 ], [ -122.47658.
.5758323125, 37.784649237923851, 0.0 ], [ -122.476849622729347,
37.784637268897804, 0.0 ] ] ] } }
(1 row)
postgres=# select * from t3 where data->'properties'->>'MAPBLKLOT' =
'1245061' and data->'properties'->>'LOT_NUM' = '063';;
data
-
{ "type": "Feature", "properties": { "MAPBLKLOT": "1245061", "BLKLOT":
"1245063", "BLOCK_NUM": "1245", "LOT_NUM": "063", "FROM_ST":.
. null, "TO_ST": null, "STREET": null, "ST_TYPE": null, "ODD_EVEN": null },
"geometry": null }
(1 row)
table dump is downloadable from http://pgsql.cz/data/data.dump.gz
Regards
Pavel
>
> Thanks
> --
> Peter Geoghegan
>
Re: [HACKERS] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3
On Tue, May 20, 2014 at 12:59:31PM -0600, Jeff Ross wrote: > Removing support functions from new cluster ok > Copying user relation files > /var/lib/postgresql/8.4/main/base/4275487/4278965 > Mismatch of relation OID in database "FNBooking": old OID 4279499, > new OID 19792 > Failure, exiting OK, those numbers are supposed to match. The array is ordered by OID and pg_upgrade expects a 1-to-1 mapping. > On 8.4.21, here's that OID: > > postgres=# \c "FNBooking" > psql (9.3.4, server 8.4.21) > You are now connected to database "FNBooking" as user "postgres". > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 4279499; > relname| relfilenode | relkind > ---+-+- > abandone_conv | 4279499 | r > (1 row) > > and on 9.3.4 it is the same: > > postgres@vdev1commandprompt2:~$ psql "FNBooking" > psql (9.3.4) > Type "help" for help. > > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 4279499; > relname| relfilenode | relkind > ---+-+- > abandone_conv | 4279499 | r > (1 row) Yes, they are supposed to match. > On 8.4.21, the new OID doesn't exist: > > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 19792; > relname | relfilenode | relkind > -+-+- > (0 rows) > > and on 9.3.4 it is this: > > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 19792; > relname | relfilenode | relkind > --+-+- > pg_toast_4279527 | 19792 | t > (1 row) > > Just to check, I did a pg_dump of the 8.4.21 FNBooking database and > it restored with psql to 9.3.4 with no issues but the overall > migration will really be too big to go this route. So the problem is that some table in the new cluster got a low-numbered toast file and the version of the table in the old cluster probably doesn't have a toast file. Can you track down details on what table owns that toast file? Can you check on the table's layout to see what might have caused the toast table creation? Were columns added/removed? If you remove that table, does pg_upgrade then work? I am guessing it would. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] jsonb nested values and indexes
Hello
I don't know a doc about jsonb indexes
"But in jsonb_path_ops, each index item is a hash of both the value and the
key(s) leading to it; for example to index {"foo": {"bar": "baz"}}, a
single index item would be created incorporating all three of foo, bar, and
baz into the hash value. Thus a containment query looking for this
structure would result in an extremely specific index search; but there is
no way at all to find out whether foo appears as a key. On the other hand,
a jsonb_ops index would create three index items representing foo, bar, and
baz separately;"
What keys I can search on this jsonb?
postgres=# create table x(a jsonb);
CREATE TABLE
postgres=# insert into x values('{"foo": {"bar": "baz"}}');
INSERT 0 1
postgres=# select * from x where a ? 'foo';
a
-
{"foo": {"bar": "baz"}}
(1 row)
postgres=# select * from x where a ? 'bar';
a
---
(0 rows)
postgres=# select * from x where a ? 'baz';
a
---
(0 rows)
???
Regards
Pavel
Re: [HACKERS] buildfarm animals and 'snapshot too old'
On 05/20/2014 03:59 PM, Gavin Flower wrote: On 21/05/14 01:42, Tom Lane wrote: Andrew Dunstan writes: On 05/20/2014 07:09 AM, Tom Lane wrote: Robert's got a point here. In my usage, the annoying thing is not animals that take a long time to report in; it's the ones that lie about the snapshot time (which is how you get "512abc4 in the middle of a bunch of ef9ab5f's"). That is an issue of incorrect system clock, not of how long it takes to do the run. I wonder if the buildfarm script could be taught to get the timestamp from an NTP server somewhere? Or at least sanity-check the system clock reading by comparing it to the newest commit timestamp in the git repo. Regarding clock skew, I think we can do better then what you suggest. The web transaction code in the client adds its own timestamp just before running the web transaction. It would be quite reasonable to reject reports from machines with skewed clocks based on this value. I'm not sure what a reasonable skew might be. Somewhere in the range of 5 to 15 minutes seems reasonable. Rather than reject, why not take the result and adjust the claimed start timestamp by the difference between the web transaction timestamp and the buildfarm server's time? I think, that if possible, any such adjustment should be noted along with the original time, so that: 1. the timing issue can be remedied 2. it is possible to link the output to any messages in the machines log etc. I don't see how that's going to help anyone. Major clock skew is a sign of client misconfiguration. And where would we note this adjustment, and who would do anything about it? We seem to be engaging in a sort of PoohBearism* here. More information is not always better. cheers andrew * Rabbit: Would you like *condensed milk*, or *honey* on your bread? Winnie the Pooh: Both. But never mind the bread, please. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] jsonb inequality operators
I have just noticed as I am preparing my slides well ahead of time :-) that we haven't documented the inequality operators of jsonb. Is that deliberate or an oversight? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3
On 5/20/14, 2:22 PM, Bruce Momjian wrote:
On Tue, May 20, 2014 at 12:59:31PM -0600, Jeff Ross wrote:
Removing support functions from new cluster ok
Copying user relation files
/var/lib/postgresql/8.4/main/base/4275487/4278965
Mismatch of relation OID in database "FNBooking": old OID 4279499,
new OID 19792
Failure, exiting
OK, those numbers are supposed to match. The array is ordered by OID
and pg_upgrade expects a 1-to-1 mapping.
Ah, so I misunderstood the error message--thanks for clearing that up.
On 8.4.21, here's that OID:
postgres=# \c "FNBooking"
psql (9.3.4, server 8.4.21)
You are now connected to database "FNBooking" as user "postgres".
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 4279499;
relname| relfilenode | relkind
---+-+-
abandone_conv | 4279499 | r
(1 row)
and on 9.3.4 it is the same:
postgres@vdev1commandprompt2:~$ psql "FNBooking"
psql (9.3.4)
Type "help" for help.
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 4279499;
relname| relfilenode | relkind
---+-+-
abandone_conv | 4279499 | r
(1 row)
Yes, they are supposed to match.
On 8.4.21, the new OID doesn't exist:
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 19792;
relname | relfilenode | relkind
-+-+-
(0 rows)
and on 9.3.4 it is this:
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 19792;
relname | relfilenode | relkind
--+-+-
pg_toast_4279527 | 19792 | t
(1 row)
Just to check, I did a pg_dump of the 8.4.21 FNBooking database and
it restored with psql to 9.3.4 with no issues but the overall
migration will really be too big to go this route.
So the problem is that some table in the new cluster got a low-numbered
toast file and the version of the table in the old cluster probably
doesn't have a toast file.
Can you track down details on what table owns that toast file? Can you
check on the table's layout to see what might have caused the toast
table creation? Were columns added/removed? If you remove that table,
does pg_upgrade then work? I am guessing it would.
Here's a sample from a different database that failed with the same problem.
Error: Mismatch of relation OID in database "UDB": old OID 1163225, new
OID 22588
postgres@vdev1commandprompt2:~$ psql "UDB"
psql (9.3.4)
Type "help" for help.
UDB=# \x
Expanded display is on.
UDB=# select * from pg_class where reltoastrelid = 22588;
-[ RECORD 1
]--+--
relname| contact_email
relnamespace | 2200
reltype| 17262
reloftype | 0
relowner | 10
relam | 0
relfilenode| 17260
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 22588
reltoastidxid | 0
relhasindex| t
relisshared| f
relpersistence | p
relkind| r
relnatts | 5
relchecks | 0
relhasoids | f
relhaspkey | t
relhasrules| f
relhastriggers | t
relhassubclass | f
relispopulated | t
relfrozenxid | 1944
relminmxid | 2
relacl |
{postgres=arwdDxt/postgres,fnv2=arwd/postgres,webv2=arwd/postgres,krish=r/postgres,fm=r/postgres}
reloptions |
UDB=# \d+ contact_email
Table
"public.contact_email"
Column |Type |
Modifiers | Storage | Stats target | Description
+-++--+--+-
id | integer | not null default
nextval('contact_email_id_seq'::regclass) | plain| |
email1 | character varying(255) | not null
| extended | |
email2 | character varying(255) |
| extended | |
time | timestamp without time zone | not null default now()
| plain| |
source | email_source| not null
| plain| |
Indexes:
"contact_email_pkey" PRIMARY KEY, btree (id)
"idx_contact_email_email1" btree (lower(email1::text)
varchar_pattern_ops)
"idx_contact_email_email2" btree (lower(email2::text)
varchar_pattern_ops)
Referenced by:
TABLE "abandoned_master_booking" CONSTRAINT
"abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY
(contact_email_id) REFERENCES contact_email(id)
TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN
KEY (contact_email_id) REFERENCES contact_email(id)
TABLE "eticketaccesslog" CONSTRAINT
"eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id)
REFERE
Re: [HACKERS] jsonb inequality operators
On May 20, 2014, at 4:39 PM, Andrew Dunstan wrote: > I have just noticed as I am preparing my slides well ahead of time :-) that > we haven't documented the inequality operators of jsonb. Is that deliberate > or an oversight? That’s gotta be an oversight. The hash and btree index support is documented, which implies those operators, yes? David signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [HACKERS] jsonb inequality operators
"David E. Wheeler" writes: > On May 20, 2014, at 4:39 PM, Andrew Dunstan wrote: >> I have just noticed as I am preparing my slides well ahead of time :-) that >> we haven't documented the inequality operators of jsonb. Is that deliberate >> or an oversight? > ThatÂs gotta be an oversight. The hash and btree index support is > documented, which implies those operators, yes? Those opclasses don't actually *require* any inequality operator --- but a quick look in the catalogs says there is one. So yeah, an oversight. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SP-GiST bug.
Hi!
create table xxx ( t text);
insert into xxx select 'x' || v::text from generate_series(1, 291) as v;
insert into xxx values ('');
create index xxxidx on xxx using spgist (t);
And postgres will eat memory forever. It seems to me that checkAllTheSame
wrongly decides that all tuples are the same. All tuples except tuple to be
inserted have the same character 'x' and only new tuple has '\0'. But
checkAllTheSame() removes new tuple because set of tuples is too big to fit page
and founds that all tuples are the same. Patch attached, suppose, all branches
with spgist should be fixed.
Original data is too big to send in e-mail list or even send link, and the bug
caused not in root page as in example above.
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
spgist_allthesame.patch.gz
Description: Unix tar archive
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb failed assertions
On Tue, May 20, 2014 at 4:17 PM, Pavel Stehule wrote: > table dump is downloadable from http://pgsql.cz/data/data.dump.gz This looks like an over-zealous assertion, without any user-visible consequences. Mea culpa. Attached patch corrects the problem. I also noticed in passing that there is another obsolete comment -- formIterIsContainer() is former jsonbIteratorNext() infrastructure, which the updated comment now refers to directly. Thanks -- Peter Geoghegan *** a/src/backend/utils/adt/jsonb_util.c --- b/src/backend/utils/adt/jsonb_util.c *** static void uniqueifyJsonbObject(JsonbVa *** 62,74 * * There isn't a JsonbToJsonbValue(), because generally we find it more * convenient to directly iterate through the Jsonb representation and only ! * really convert nested scalar values. formIterIsContainer() does this, so ! * that clients of the iteration code don't have to directly deal with the ! * binary representation (JsonbDeepContains() is a notable exception, although ! * all exceptions are internal to this module). In general, functions that ! * accept a JsonbValue argument are concerned with the manipulation of scalar ! * values, or simple containers of scalar values, where it would be ! * inconvenient to deal with a great amount of other state. */ Jsonb * JsonbValueToJsonb(JsonbValue *val) --- 62,74 * * There isn't a JsonbToJsonbValue(), because generally we find it more * convenient to directly iterate through the Jsonb representation and only ! * really convert nested scalar values. JsonbIteratorNext() does this, so that ! * clients of the iteration code don't have to directly deal with the binary ! * representation (JsonbDeepContains() is a notable exception, although all ! * exceptions are internal to this module). In general, functions that accept ! * a JsonbValue argument are concerned with the manipulation of scalar values, ! * or simple containers of scalar values, where it would be inconvenient to ! * deal with a great amount of other state. */ Jsonb * JsonbValueToJsonb(JsonbValue *val) *** compareJsonbContainers(JsonbContainer *a *** 198,212 * * If the two values were the same container type, then there'd * have been a chance to observe the variation in the number of ! * elements/pairs (when processing WJB_BEGIN_OBJECT, say). They ! * can't be scalar types either, because then they'd have to be ! * contained in containers already ruled unequal due to differing ! * numbers of pairs/elements, or already directly ruled unequal ! * with a call to the underlying type's comparator. */ Assert(va.type != vb.type); ! Assert(va.type == jbvArray || va.type == jbvObject); ! Assert(vb.type == jbvArray || vb.type == jbvObject); /* Type-defined order */ res = (va.type > vb.type) ? 1 : -1; } --- 198,210 * * If the two values were the same container type, then there'd * have been a chance to observe the variation in the number of ! * elements/pairs (when processing WJB_BEGIN_OBJECT, say). They're ! * either two heterogeneously-typed containers, or a container and ! * some scalar type. */ Assert(va.type != vb.type); ! Assert(va.type != jbvBinary); ! Assert(vb.type != jbvBinary); /* Type-defined order */ res = (va.type > vb.type) ? 1 : -1; } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3
On Tue, May 20, 2014 at 03:25:00PM -0600, Jeff Ross wrote: > Here's a sample from a different database that failed with the same problem. > > Error: Mismatch of relation OID in database "UDB": old OID 1163225, > new OID 22588 > postgres@vdev1commandprompt2:~$ psql "UDB" > psql (9.3.4) > Type "help" for help. > > UDB=# \x > Expanded display is on. > UDB=# select * from pg_class where reltoastrelid = 22588; > -[ RECORD 1 > ]--+-- > relname| contact_email > > UDB=# \dT+ email_source > List of data types > -[ RECORD 1 ]-+- > Schema| public > Name | email_source > Internal name | email_source > Size | 4 > Elements | Booking > | User Profile > | UserProfile > Access privileges | =U/postgres > Description | > > I do not know if columns were added or removed. > > Dropping the table from the last database that caused pg_upgrade to > fail let pg_upgrade proceed on through many more before it failed > again on the UDB database, so that's progress! > > If there is anything else I can provide, let me know. OK, so we have code in the backend to force a toast table in the new cluster if there isn't one in the old cluster, e.g.: /* * Check to see whether the table actually needs a TOAST table. * * If an update-in-place toast relfilenode is specified, force toast file * creation even if it seems not to need one. */ if (!needs_toast_table(rel) && (!IsBinaryUpgrade || !OidIsValid(binary_upgrade_next_toast_pg_class_oid))) return false; What we never considered is case where the old cluster didn't have one and the new one does. What I would like you to do is to use the 9.3 pg_dump and create a schema-only dump: pg_dump --schema-only --binary-upgrade Find the table that is getting the toast file on the new cluster but not the old one, and run all the commands related to that table from the dump --- you don't need to load any data, just the schema items. Run that on the old cluster and the new cluster, then check if you are getting the same case where there is no toast table on the old cluster but one on the new cluster. Remember to only use the 9.3 pg_dump output for both old and new clusters. Thanks. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb failed assertions
2014-05-21 3:22 GMT+02:00 Peter Geoghegan : > On Tue, May 20, 2014 at 4:17 PM, Pavel Stehule > wrote: > > table dump is downloadable from http://pgsql.cz/data/data.dump.gz > > This looks like an over-zealous assertion, without any user-visible > consequences. Mea culpa. > > Attached patch corrects the problem. I also noticed in passing that > there is another obsolete comment -- formIterIsContainer() is former > jsonbIteratorNext() infrastructure, which the updated comment now > refers to directly. > > Thanks > It works Thank you Regards Pavel > -- > Peter Geoghegan >
Re: [HACKERS] 9.4 checksum error in recovery with btree index
On Tue, May 20, 2014 at 5:10 AM, Jeff Janes wrote: > What would be a good way of doing that? Mostly I've just been sharing it > on google drive when I find something. Should I fork the mirror from > github (https://github.com/postgres/postgres)? Forking the entire > codebase just to have a 200 line patch and 2 driving scripts seems > excessive, but I guess that that is the git philosophy. Or is there a > better way than that? > When forking a repository for development purposes, usually the whole repository is not necessary: push only the master branch that you update from time to time and create separate branches for each development patch. If people would like to have a look at your patch, they would simply need to add a remote link to your fork, fetching only the diffs introduced by your additional branch(es) and then to checkout the branch on which your patch is. This has the advantage to let the user merge himself the code with newer code and not getting failures when applying a patch that will get chunks of code rejected over time. My 2c. -- Michael
