Re: [GENERAL] cache lookup failed for index

[Tom Lane]

Willy-Bas Loos  writes:
> [ pg_dump sometimes fails with ]
> pg_dump: [archiver (db)] query failed: ERROR:  cache lookup failed for
> index 231808363

This wouldn't be too surprising if you're constantly creating and dropping
indexes.  There's a small window between where pg_dump starts its
transaction and where it's able to acquire lock on each table; but since
it's working from a transaction-start-time view of the catalogs, it would
still expect the table to have all the indexes it did at the start.

If you've got a lot of DDL going on, maybe the window wouldn't even be
that small: pg_dump's attempt to lock some previous table might've blocked
for awhile due to DDL on that one.

regards, tom lane


-- 
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 index

[Willy-Bas Loos]

On Tue, Jun 28, 2016 at 7:14 PM, Willy-Bas Loos  wrote:

>
> (...)
> Does anyone know what's up?
> --
>
>
oh btw this is postgres 9.3 on debian 7
and londiste 2

-- 
Willy-Bas Loos

Re: [GENERAL] jsonb search

[Arthur Silva]

On Tue, Jun 28, 2016 at 5:09 PM, Oleg Bartunov  wrote:

> On Tue, Jun 28, 2016 at 5:15 PM, Armand Pirvu (home)
>  wrote:
> > Hi
> >
> > In my quest of JSONB querying and searching without having to actually
> cast
> > into a text, I found JSQuery
> >
> > I do admit my JSONB knowledge shortcoming and I am not a developer but a
> > DBA. As such some examples would be greatly appreciated since I tend to
> > understand better
> >
> > I compiled and installed the extension
> >
> > 1 - Exact matching without knowing the hierarchy, just the key and
> element,
> > I built a set like
> >
> > col1 |   col2
> > --+--
> >1 | {"Home Email": {"EmailAddress": "1...@yahoo.com"}}
> >2 | {"Home Email": {"EmailAddress": "2...@yahoo.com"}}
> >3 | {"Home Email": {"EmailAddress": "3...@yahoo.com"}}
> >
> >
> > JSQuqery is super
> >
> > SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@yahoo.com"';
> >
> > Now I can do a performance boost using
> >
> > CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops);
> >
> > I see this yield
> >
> > from
> >
> > testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@
> '*.EmailAddress
> > = "1...@yahoo.com"';
> > Seq Scan on test1  (cost=0.00..12423.00 rows=500 width=68) (actual
> > time=0.016..160.777 rows=1 loops=1)
> >   Filter: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
> >   Rows Removed by Filter: 49
> > Planning time: 0.042 ms
> > Execution time: 160.799 ms
> > (5 rows)
> >
> >
> > to
> >
> > testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress =
> > "1...@yahoo.com"';
> > Bitmap Heap Scan on test1  (cost=31.88..1559.32 rows=500 width=68)
> (actual
> > time=0.018..0.019 rows=1 loops=1)
> >   Recheck Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
> >   Heap Blocks: exact=1
> >   ->  Bitmap Index Scan on idx1  (cost=0.00..31.75 rows=500 width=0)
> (actual
> > time=0.011..0.011 rows=1 loops=1)
> > Index Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com
> "'::jsquery)
> > Planning time: 0.039 ms
> > Execution time: 0.038 ms
> > (7 rows)
> >
> > A whooping 4000 times improvement
> >
> >
> >
> >
> > But I also noticed a vodka index
> >
> >
> > testdb=# CREATE INDEX idx2 ON
> > testdb-# test1 USING vodka (col2);
> > ERROR:  access method "vodka" does not exist
> >
> > What am I missing ?
> >
> > 2 - Is there anyway I can accomplish a pattern and/or case insensitive
> > search using JSQuery similar to
> >
> >
> > select * from test2 where upper((col2 -> 'Home Email') ->>
> 'EmailAddress')
> > ilike '%3%YAH%';
> >
> > select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like
> > '%3%yah%';
> >
> >
> > If so what indexing strategy can be used to have similar gains as above ?
> >
> >
> > Many thanks for any help
>
> Vodka is our experimental prototype of access method of next
> generation and it doesn't exists in production-ready form. You can
> check our presentation
> http://www.sai.msu.su/~megera/postgres/talks/highload-2014-vodka.pdf
> to understand jsquery limitation and why we stop its development.
> Also, 2 years ago I wrote (in russian)
> http://obartunov.livejournal.com/179422.html about jsonb query
> language and our plans. Google translate might helps
>
>
> https://translate.google.com/translate?sl=auto=en=y=_t=en=UTF-8=http%3A%2F%2Fobartunov.livejournal.com%2F179422.html==url
>
>
> >
> >
> > Armand
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Could you share your future plans for it (or it's reincarnation), if any?

Even in the limited form, vodka is very impressive.

--
Arthur Silva

[GENERAL] cache lookup failed for index

[Willy-Bas Loos]

Hi,

I have a londiste consumer database that has some additional user data in
it.
The user data is in schema's with the prefix oz_
Every night we dump those schema's with pg_dump.

About 2-3 times per week cron emails me that something went wrong.
That means that 4-5 day per week, everything works fine. The data is there
too, i haven't yet been able to look if anything's missing when it goes
wrong.

This is the error:
-
pg_dump: [archiver (db)] query failed: ERROR:  cache lookup failed for
index 231808363
pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname AS
indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, t.relnatts
AS indnkeys, i.indkey, i.indisclustered, false AS indisreplident,
t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred,
c.tableoid AS contableoid, c.oid AS conoid,
pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname
FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS
tablespace, t.reloptions AS indreloptions FROM pg_catalog.pg_index i JOIN
pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN
pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid =
c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid =
'231800968'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY
indexname
-
The index number is a different one every time.
The log says the same thing.

interesting detail:
On  the days that i have successful dumps from, the last dump is finished
within 1:15 hours.
But the errors occur at very different times, sometimes 14 hours after the
job started (or maybe it started on a different day).
But munin doesn't show clear peak loads on the server.


And this is the script:
-
#!/bin/bash

HOST=localhost
PGPORT=5432
BACKUPDIR=/data/dump/afolder
DATABASE=adatabase

SCHEMAS=`psql -p $PGPORT $DATABASE -t --command "select schema_name from
information_schema.schemata where schema_name LIKE 'oz_%'"`

for SCHEMA in $SCHEMAS
do
#mv $BACKUPDIR/$SCHEMA.backup.1 $BACKUPDIR/$SCHEMA.backup.2
mv $BACKUPDIR/$SCHEMA.backup $BACKUPDIR/$SCHEMA.backup.1
pg_dump -Fc -Z3 -p $PGPORT -n $SCHEMA $DATABASE >
$BACKUPDIR/$SCHEMA.backup
done
-

It seems like an index gets deleted while pg_dump has it in some task list,
and by the time pg_dump wants to dump it, it's gone.
But that should not be possible, because of transactions.

Does anyone know what's up?
-- 
Willy-Bas Loos

Re: [GENERAL] questions about how to implement a gist index

[Riccardo Vianello]

Hi Oleg,

On Tue, Jun 28, 2016 at 1:05 AM, Oleg Bartunov  wrote:

> On Tue, Jun 28, 2016 at 12:44 AM, Riccardo Vianello
>  wrote:
> > Could you please also help me understand the difference (if any) between
> > using the GIST_LEAF macro or the leafkey attribute of the GISTENTRY data
> > structure?
>
> Yes, this is confused.
>
> GIST_LEAF is TRUE if key is in leaf page.
>
> bool leafkey points if TRUE that key contains value from heap.
>

I think I wrongly assumed that internal and leaf nodes are homogeneously
filled with union and indexed values respectively. Is it correct that
leafkey is always TRUE for the entries that populate a leaf page, and may
be either TRUE and FALSE if GIST_LEAF is FALSE?

Thanks,
Riccardo

Re: [GENERAL] jsonb search

[Oleg Bartunov]

On Tue, Jun 28, 2016 at 5:15 PM, Armand Pirvu (home)
 wrote:
> Hi
>
> In my quest of JSONB querying and searching without having to actually cast
> into a text, I found JSQuery
>
> I do admit my JSONB knowledge shortcoming and I am not a developer but a
> DBA. As such some examples would be greatly appreciated since I tend to
> understand better
>
> I compiled and installed the extension
>
> 1 - Exact matching without knowing the hierarchy, just the key and element,
> I built a set like
>
> col1 |   col2
> --+--
>1 | {"Home Email": {"EmailAddress": "1...@yahoo.com"}}
>2 | {"Home Email": {"EmailAddress": "2...@yahoo.com"}}
>3 | {"Home Email": {"EmailAddress": "3...@yahoo.com"}}
>
>
> JSQuqery is super
>
> SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@yahoo.com"';
>
> Now I can do a performance boost using
>
> CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops);
>
> I see this yield
>
> from
>
> testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@ '*.EmailAddress
> = "1...@yahoo.com"';
> Seq Scan on test1  (cost=0.00..12423.00 rows=500 width=68) (actual
> time=0.016..160.777 rows=1 loops=1)
>   Filter: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
>   Rows Removed by Filter: 49
> Planning time: 0.042 ms
> Execution time: 160.799 ms
> (5 rows)
>
>
> to
>
> testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress =
> "1...@yahoo.com"';
> Bitmap Heap Scan on test1  (cost=31.88..1559.32 rows=500 width=68) (actual
> time=0.018..0.019 rows=1 loops=1)
>   Recheck Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
>   Heap Blocks: exact=1
>   ->  Bitmap Index Scan on idx1  (cost=0.00..31.75 rows=500 width=0) (actual
> time=0.011..0.011 rows=1 loops=1)
> Index Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
> Planning time: 0.039 ms
> Execution time: 0.038 ms
> (7 rows)
>
> A whooping 4000 times improvement
>
>
>
>
> But I also noticed a vodka index
>
>
> testdb=# CREATE INDEX idx2 ON
> testdb-# test1 USING vodka (col2);
> ERROR:  access method "vodka" does not exist
>
> What am I missing ?
>
> 2 - Is there anyway I can accomplish a pattern and/or case insensitive
> search using JSQuery similar to
>
>
> select * from test2 where upper((col2 -> 'Home Email') ->> 'EmailAddress')
> ilike '%3%YAH%';
>
> select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like
> '%3%yah%';
>
>
> If so what indexing strategy can be used to have similar gains as above ?
>
>
> Many thanks for any help

Vodka is our experimental prototype of access method of next
generation and it doesn't exists in production-ready form. You can
check our presentation
http://www.sai.msu.su/~megera/postgres/talks/highload-2014-vodka.pdf
to understand jsquery limitation and why we stop its development.
Also, 2 years ago I wrote (in russian)
http://obartunov.livejournal.com/179422.html about jsonb query
language and our plans. Google translate might helps

https://translate.google.com/translate?sl=auto=en=y=_t=en=UTF-8=http%3A%2F%2Fobartunov.livejournal.com%2F179422.html==url


>
>
> Armand
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] jsonb search

[Armand Pirvu (home)]

Hi

In my quest of JSONB querying and searching without having to actually cast 
into a text, I found JSQuery

I do admit my JSONB knowledge shortcoming and I am not a developer but a DBA. 
As such some examples would be greatly appreciated since I tend to understand 
better

I compiled and installed the extension

1   -   Exact matching without knowing the hierarchy, just the key and 
element, I built a set like

col1 |   col2   
--+--
   1 | {"Home Email": {"EmailAddress": "1...@yahoo.com"}}
   2 | {"Home Email": {"EmailAddress": "2...@yahoo.com"}}
   3 | {"Home Email": {"EmailAddress": "3...@yahoo.com"}}


JSQuqery is super 

SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@yahoo.com"';

Now I can do a performance boost using

CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops);

I see this yield

from 

testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = 
"1...@yahoo.com"';
Seq Scan on test1  (cost=0.00..12423.00 rows=500 width=68) (actual 
time=0.016..160.777 rows=1 loops=1)
  Filter: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
  Rows Removed by Filter: 49
Planning time: 0.042 ms
Execution time: 160.799 ms
(5 rows)


to

testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@yahoo.com"';
Bitmap Heap Scan on test1  (cost=31.88..1559.32 rows=500 width=68) (actual 
time=0.018..0.019 rows=1 loops=1)
  Recheck Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx1  (cost=0.00..31.75 rows=500 width=0) (actual 
time=0.011..0.011 rows=1 loops=1)
Index Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
Planning time: 0.039 ms
Execution time: 0.038 ms
(7 rows)

A whooping 4000 times improvement




But I also noticed a vodka index 


testdb=# CREATE INDEX idx2 ON
testdb-# test1 USING vodka (col2);
ERROR:  access method "vodka" does not exist

What am I missing ?

2   -   Is there anyway I can accomplish a pattern and/or case 
insensitive search using JSQuery similar to 


select * from test2 where upper((col2 -> 'Home Email') ->> 'EmailAddress') 
ilike '%3%YAH%';

select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like 
'%3%yah%';


If so what indexing strategy can be used to have similar gains as above ? 


Many thanks for any help


Armand