[GENERAL] Cache lookup failed for type 70385664
For a certain record in our database I'm getting cache lookup failures (ERROR: cache lookup failed for type 70385664). And only for one of the 2 array columns in that record. The table definition is: \d inhoudingen Table "public.inhoudingen" Column|Type | Modifiers --+-+--- verkoop_id | bigint | not null regel| smallint| not null bedrag | numeric(12,2) | not null berekend_op | timestamp(0) with time zone | berekend_bedrag | numeric(12,2) | comp_bedrag | numeric(12,4)[] | comp_naam| character varying(25)[] | omschrijving | character varying(100) | Indexes: "geen_dubbele_idx" PRIMARY KEY, btree (verkoop_id, regel) Foreign-key constraints: "inhoudingen_fk" FOREIGN KEY (verkoop_id) REFERENCES verkoop(id) ON UPDATE CASCADE ON DELETE CASCADE "inhoudingen_fk1" FOREIGN KEY (regel) REFERENCES regels(id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT And the column comp_naam is giving this error for one specific record. The database was put in shutdown mode today without a reason (as in, we don't know why it did that, yet) and since then it's giving this error. I'm now wondering what I can do to fix this. Do I need to delete that record, or is there a way to recover it? Regards, Wessel van Norel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cache lookup failed for type 70385664
Oh, probably found the cause of the sudden restarts: Jun 23 09:39:13 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[25239] core dumped: /var/core/core_global.postgres Jun 23 09:45:56 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[26455] core dumped: /var/core/core_global.postgres Jun 23 09:56:22 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[29292] core dumped: /var/core/core_global.postgres Jun 23 10:18:00 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[3838] core dumped: /var/core/core_global.postgres Jun 23 10:27:23 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[5653] core dumped: /var/core/core_global.postgres Jun 23 10:38:06 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[7929] core dumped: /var/core/core_global.postgres Jun 23 10:49:28 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[10192] core dumped: /var/core/core_global.postgres Jun 23 10:58:59 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[12072] core dumped: /var/core/core_global.postgres Jun 23 11:05:57 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[13262] core dumped: /var/core/core_global.postgres Jun 23 11:18:04 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[16759] core dumped: /var/core/core_global.postgres Jun 23 11:30:25 ph1phys01 genunix: [ID 603404 kern.notice] NOTICE: core_log: postgres[19267] core dumped: /var/core/core_global.postgres It's on a solaris 10 system. pstack of the last coredump gives me: core '/var/core/core_global.postgres' of 19267: /usr/bin/postgres -D /postgresql/server/db_ph 080833ed slot_deform_tuple (8507718, 8560640, 8500da8, 0, 0, 8560e48) + 1a9 0850 () Not sure if that's of any help though... We are running postgresql 8.2.3 on solaris 10. Regards, Wessel van Norel On Tue, Jun 23, 2009 at 12:37 PM, DelGurth wrote: > For a certain record in our database I'm getting cache lookup failures > (ERROR: cache lookup failed for type 70385664). And only for one of > the 2 array columns in that record. > > The table definition is: > > \d inhoudingen >Table "public.inhoudingen" >Column|Type | Modifiers > --+-+--- > verkoop_id | bigint | not null > regel| smallint| not null > bedrag | numeric(12,2) | not null > berekend_op | timestamp(0) with time zone | > berekend_bedrag | numeric(12,2) | > comp_bedrag | numeric(12,4)[] | > comp_naam| character varying(25)[] | > omschrijving | character varying(100) | > Indexes: >"geen_dubbele_idx" PRIMARY KEY, btree (verkoop_id, regel) > Foreign-key constraints: >"inhoudingen_fk" FOREIGN KEY (verkoop_id) REFERENCES verkoop(id) > ON UPDATE CASCADE ON DELETE CASCADE >"inhoudingen_fk1" FOREIGN KEY (regel) REFERENCES regels(id) MATCH > FULL ON UPDATE CASCADE ON DELETE RESTRICT > > > And the column comp_naam is giving this error for one specific record. > > The database was put in shutdown mode today without a reason (as in, > we don't know why it did that, yet) and since then it's giving this > error. > > I'm now wondering what I can do to fix this. Do I need to delete that > record, or is there a way to recover it? > > Regards, > Wessel van Norel >
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Thu, Oct 16, 2008 at 4:40 PM, Stephen Frost <[EMAIL PROTECTED]> wrote: > * Tomasz Ostrowski ([EMAIL PROTECTED]) wrote: > I don't see 'limit 1' anywhere in that patch.. And you don't want to > use 'limit 1' *and* count(*), that doesn't do what you're expecting > (since count(*) is an aggregate and limit 1 is applied after). You > really want to do something more like: > > select true from tab1 limit 1; > > And then test if you got back any rows or not (which might be what > *your* patch does, hadn't looked at it yet). > >Thanks, > >Stephen Seems Tomasz linked to the wrong patch. The patch he meant was: http://drupal.org/files/issues/drupal_lookup_path-6.x.patch.txt Or better... he linked to the correct patch (for drupal 5.x) but the user Earnie made a mistake or something in the 5.x patch, seeing the comment[1] that the patches should be the same... Also nice to see people "benchmark" differences by just executing a query once[2][3] Wessel [1] http://drupal.org/node/196862#comment-648511 [2] http://drupal.org/node/196862#comment-649791 [3] http://drupal.org/node/196862#comment-649928 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sequence skips 30 values, how?
On 1/31/07, Geoffrey <[EMAIL PROTECTED]> wrote: We are trying to track down an issue with our PostgreSQL application. We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. We have a situation where the postgres backend process drops core and dies. We've tracked this to an unusual situation where a sequence value that is being created during the process that is causing the core file generation. The thing that is bizarre is that the sequence value skips 30+ entries. How is this even possible? Any suggestions would be greatly appreciated. In addition to previous suggestions (rollback/server crashes), perhaps a hint where you can look at. I personally had an experience like this with sequences in Oracle. An default oracle sequence caches 20 records in memory, which caused the unused records to be "gone" on a server reboot, and worse, in case the sequence was "swapped" out of memory because it wasn't used for a while. I don't know if you used the "cache" statement while creating your sequence. But in case you did, this can also explain why you have "missing" sequence records. But you must have explicitly set the cache option, since the default of postgresql is 1 [1], not 20 [2]. Please note, my findings with cache are based on the behavour of Oracle. I didn't test this with postgresql. Regards, Wessel van Norel [1] http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html [2] http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6014.htm#sthref5342 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] sequence skips 30 values, how?
Slightly OT. That documentation page of postgresql contains an invalid
example. Not sure if I should report it in here, but well, there you
go.
CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
nextval
-
114
So you start at 101 and get 114, how nice ;-)
Regards,
Wessel van Norel
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Queries joining views
Alban Hertroys writes:> Is there a trick to make this work a bit faster? Have you really shown us the right queries for those explain results?I don't see where the second plan is testing "dir <> 1" at all.It looks like the first one is faster because it's using a partial index that has predicate dir <> 1, while the second one is usinga much larger full index. But I don't see where the second planis applying that restriction, so I wonder if you forgot it in thequery. regards, tom laneHe has really shown the right queries. But I see the table definition if mm_insrel_table (including the indexes) is not in the e-mail, so you don't see why the dir <> 1 is not in the query plan. Here is the table definition, with the indexes. As you can see we tried some indexes, to see if we could get the queries on the views to become faster. zorgweb_solaris=> \d mm_insrel_tableTable "public.mm_insrel_table" Column | Type | Modifiers-+-+--- number | integer | not null snumber | integer | not null dnumber | integer | not null rnumber | integer | not null dir | integer |Indexes: "mm_insrel_table_pkey" PRIMARY KEY, btree (number) "mm_insrel_dir_not_one_idx" btree (dnumber, snumber) WHERE dir <> 1 "mm_insrel_dnumber_dir_not_one_idx" btree (dnumber) WHERE dir <> 1 "mm_insrel_full_idx" btree (snumber, dnumber, rnumber, dir) WHERE dir <> 1 "mm_insrel_relation_idx" btree (snumber, dnumber, rnumber) Foreign-key constraints: "mm_insrel_table_dnumber_fkey" FOREIGN KEY (dnumber) REFERENCES mm_object(number) "mm_insrel_table_rnumber_fkey" FOREIGN KEY (rnumber) REFERENCES mm_object(number) "mm_insrel_table_snumber_fkey" FOREIGN KEY (snumber) REFERENCES mm_object(number)I hope this explains you why the dir <> 1 is not in the view query. Why the other query plan thinks it needs to recheck the condition is not clear to me, but I'm not an expert on PostgreSQL query plans. Regards,Wessel van Norel
Re: [GENERAL] Queries joining views
BTW, what PG version is this exactly?Our PG version is the version downloadable from http://www.sunfreeware.com/programlistsparc10.html#postgresql , so 8.0.1 for solaris sparc.Sorry I was wrong on this point, it's 8.1.4-bash-3.00$ pg_config --versionPostgreSQL 8.1.4And it's the version from blastwave.org: http://www.blastwave.org/packages.php/postgresqlRegards, Wessel van Norel
Re: [GENERAL] Queries joining views
On 8/21/06, Tom Lane <[EMAIL PROTECTED]> wrote: Hmph ... it certainly appears to be choosing the wrong index in thesecond case. I wonder why --- can you show the relpages and reltuplesstats from pg_class for these indexes?I'm personally not aware how to do that, perhaps Alban will (tell me how to) do that tomorrow. It might be interesting also to examine the output of justexplain select * from mm_insrel_table where dnumber=558332 and dir<>1with different subsets of these indexes in place. I'd like to see what it's deriving as the cost estimates for these indexes. If you can getan EXPLAIN selecting each one of the indexes, that would help diagnosewhat's going on.Ok. Did that (with your trick, thanks!). The output is attached to this e-mail. The script I used to drop the indexes dropped them in the order it was using them (partially by accident, partially because I assumed it would work in that order). I'm not sure if you want to see more permutations, if so please tell me. BTW, what PG version is this exactly?Our PG version is the version downloadable from http://www.sunfreeware.com/programlistsparc10.html#postgresql , so 8.0.1 for solaris sparc.(it's a sun T2000 test machine). regards, tom laneRegards,Wessel van Norel begin; explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1; rollback; QUERY PLAN --- Bitmap Heap Scan on mm_insrel_table (cost=2.11..113.50 rows=30 width=20) (actual time=0.089..0.096 rows=2 loops=1) Recheck Cond: ((dnumber = 558332) AND (dir <> 1)) -> Bitmap Index Scan on mm_insrel_dnumber_dir_not_one_idx (cost=0.00..2.11 rows=30 width=0) (actual time=0.070..0.070 rows=2 loops=1) Index Cond: (dnumber = 558332) Total runtime: 0.238 ms (5 rows) begin; drop index mm_insrel_dnumber_dir_not_one_idx; explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1; rollback; QUERY PLAN --- Bitmap Heap Scan on mm_insrel_table (cost=2.11..113.50 rows=30 width=20) (actual time=0.084..0.091 rows=2 loops=1) Recheck Cond: ((dnumber = 558332) AND (dir <> 1)) -> Bitmap Index Scan on mm_insrel_dir_not_one_idx (cost=0.00..2.11 rows=30 width=0) (actual time=0.067..0.067 rows=2 loops=1) Index Cond: (dnumber = 558332) Total runtime: 0.226 ms (5 rows) begin; drop index mm_insrel_dnumber_dir_not_one_idx; drop index mm_insrel_dir_not_one_idx; explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1; rollback; QUERY PLAN Index Scan using mm_insrel_full_idx on mm_insrel_table (cost=0.00..2533.97 rows=30 width=20) (actual time=43.965..162.962 rows=2 loops=1) Index Cond: (dnumber = 558332) Total runtime: 163.085 ms (3 rows) begin; drop index mm_insrel_dnumber_dir_not_one_idx; drop index mm_insrel_dir_not_one_idx; drop index mm_insrel_full_idx; explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1; rollback; QUERY PLAN Index Scan using mm_insrel_relation_idx on mm_insrel_table (cost=0.00..3102.97 rows=30 width=20) (actual time=44.608..164.894 rows=2 loops=1) Index Cond: (dnumber = 558332) Filter: (dir <> 1) Total runtime: 165.025 ms (4 rows) begin; drop index mm_insrel_dnumber_dir_not_one_idx; drop index mm_insrel_dir_not_one_idx; drop index mm_insrel_full_idx; drop index mm_insrel_relation_idx; explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1; rollback; QUERY PLAN -- Seq Scan on mm_insrel_table (cost=0.00..8160.42 rows=30 width=20) (actual time=855.286..884.919 rows=2 loops=1) Filter: ((dnumber = 558332) AND (dir <> 1)) Total runtime: 885.042 ms (3 rows) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
