[GENERAL] Unexpected planner behavior with *_pattern_ops index matching
Greetings.
I'm in the process of indexing a virtual file system (on 9.2.9, build
info below) and I ran into what I perceive as an inconsistency in the
way index viability is assessed by the planner.
Perhaps I'm misinterpreting the docs, but it seems like stable functions
don't behave as per
http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html when
*_pattern_ops operator classes are used (not sure about others).
Steps I followed to reproduce the anomaly:
geoop_prototype=# -- Build/platform Info:
geoop_prototype=# SELECT version();
version
--
PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
geoop_prototype=#
geoop_prototype=#
geoop_prototype=# -- Small sample of data volume/distribution from the
involved table and column.
geoop_prototype=# SELECT COUNT(0) FROM inode_segments WHERE (full_path
IS NOT NULL);
count
291019
(1 row)
geoop_prototype=# SELECT char_length(full_path) FROM inode_segments
WHERE (full_path IS NOT NULL) ORDER BY random() LIMIT 10;
char_length
-
80
126
108
75
116
71
70
76
137
113
(10 rows)
geoop_prototype=#
geoop_prototype=#
geoop_prototype=# -- Query plan without the operator class-specific
index. As expected the left anchored regex prompts a table scan
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE
full_path ~ '^/THIS/MATCHES/NOTHING/';
QUERY PLAN
---
Seq Scan on inode_segments (cost=0.00..27401.85 rows=29 width=8)
Filter: ((full_path)::text ~ '^/THIS/MATCHES/NOTHING/'::text)
(2 rows)
geoop_prototype=#
geoop_prototype=#
geoop_prototype=# -- We now define an ad-hoc index
geoop_prototype=# CREATE INDEX ix_inode_segments_filter_by_subtree ON
gorfs.inode_segments USING BTREE(full_path varchar_pattern_ops);
CREATE INDEX
geoop_prototype=#
geoop_prototype=#
geoop_prototype=# -- Same query as above. Predictably, the index is now
being scanned instead
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE
full_path ~ '^/THIS/MATCHES/NOTHING/';
QUERY PLAN
Index Scan using ix_inode_segments_filter_by_subtree on
inode_segments (cost=0.00..8.49 rows=29 width=8)
Index Cond: (((full_path)::text ~=~
'/THIS/MATCHES/NOTHING/'::text) AND ((full_path)::text ~~
'/THIS/MATCHES/NOTHING0'::text))
Filter: ((full_path)::text ~ '^/THIS/MATCHES/NOTHING/'::text)
(3 rows)
geoop_prototype=#
geoop_prototype=#
geoop_prototype=# -- However, when the comparison value comes from a
function that is marked as STABLE, the planner reverts to a full scan
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE
full_path ~ CONCAT('^/THIS/MATCHES/NOTHING/');
QUERY PLAN
-
Seq Scan on inode_segments (cost=0.00..28789.02 rows=29 width=8)
Filter: ((full_path)::text ~ concat('^/THIS/MATCHES/NOTHING/'))
(2 rows)
geoop_prototype=#
geoop_prototype=#
geoop_prototype=# -- Immutable functions are not affected...
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE
full_path ~ UPPER('^/THIS/MATCHES/NOTHING/');
QUERY PLAN
Index Scan using ix_inode_segments_filter_by_subtree on
inode_segments (cost=0.00..8.49 rows=29 width=8)
Index Cond: (((full_path)::text ~=~
'/THIS/MATCHES/NOTHING/'::text) AND ((full_path)::text ~~
'/THIS/MATCHES/NOTHING0'::text))
Filter: ((full_path)::text ~ '^/THIS/MATCHES/NOTHING/'::text)
(3 rows)
geoop_prototype=# -- ... nor are other operator classes (with
preexisting index. Note that CONCAT is again being used here)
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE
full_path CONCAT('/THIS/MATCHES/NOTHINA/');
QUERY PLAN
-
Re: [GENERAL] Appending new data to existing field of Json data type
@Adrian Klaver, Thanks for this idea but still m really confused with how
to update the json filed in the DB. is there any way to update the json
field in the DB through view.?
On Wed, Oct 29, 2014 at 7:37 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 10/29/2014 01:06 AM, VENKTESH GUTTEDAR wrote:
Ya agreed thats not legal JSON, that was typing mistake sorry for that,
let me make you clear what i need exactly,
I have table named (exampleTable) with json field as (example_list), and
when i say
SELECT * FROM exampleTable;
id | example_list
---+--
2 | {abc:[ { a:b,c:d } ] }
And this data i am inserting through DJango view by writing the
following statement
test = ExampleTable(id = 2, example_list = {abc : [ { a:b,c:d
} ] })
test.save()
now i want to append { e:f, g:h } to example_list by specifying
the id.
and after appending the data should be stored in the following way :
After appending,
for example if i say :
SELECT * FROM exampleTable;
i should get this.
id | example_list
---+
2 | {abc:[ { a:b,c:d }, { e:f, g:h } ] }
Hope your clear now.
So now Guide me to append it through Python Djnago View. or through raw
sql query.
If it where me I would bring the data into the view and do the work there
in Python using dicts and lists. There is a Python module out there that
make this easier to do:
https://pypi.python.org/pypi/django-jsonfield
--
Regards :
Venktesh Guttedar.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Regards :
Venktesh Guttedar.
Re: [GENERAL] DBlink, postgres to DB2
Hi , While i try to install psqlDDBC, i get this error , i have SUSE Linux Enterprise Server 11 , trying to find a solution still have no idea. ./configure checking for a BSD-compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking for a thread-safe mkdir -p... /bin/mkdir -p checking for gawk... gawk checking whether make sets $(MAKE)... yes checking whether to enable maintainer-specific portions of Makefiles... no checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for style of include used by make... GNU checking dependency style of gcc... gcc3 checking -Wall is a valid compile option... yes checking for odbc_config... no configure: error: odbc_config not found (required for unixODBC build) Regards Manmohan. K On Thu, Oct 23, 2014 at 2:41 PM, Миша Тюрин tmih...@bk.ru wrote: http://multicorn.org/ another cool stuff to generalize interconnections Wed, 22 Oct 2014 22:34:06 -0700 от Postgres India pgbugin...@gmail.com: Thanks a lot Remi, Merlin and Pavel...I will give it a try. On Wed, Oct 22, 2014 at 6:36 AM, Merlin Moncure mmonc...@gmail.com https://e.mail.ru/compose/?mailto=mailto%3ammonc...@gmail.com wrote: On Wed, Oct 22, 2014 at 5:56 AM, Pavel Stehule pavel.steh...@gmail.com https://e.mail.ru/compose/?mailto=mailto%3apavel.steh...@gmail.com wrote: Hi try to use a Perl implementation https://github.com/davidfetter/DBI-Link There is a DBD driver for DB2 http://search.cpan.org/dist/DBD-DB2/DB2.pod Or you can use a mentioned fdw wrapper - there is ODBC wrapper https://wiki.postgresql.org/wiki/Foreign_data_wrappers#odbc_fdw and you can access DB2 via generic ODBC DB2 driver jdbc-fdw should work too. https://github.com/atris/JDBC_FDW merlin
Re: [GENERAL] DBlink, postgres to DB2
2014-10-30 8:47 GMT+01:00 Postgres India pgbugin...@gmail.com: Hi , While i try to install psqlDDBC, i get this error , i have SUSE Linux Enterprise Server 11 , trying to find a solution still have no idea. ./configure checking for a BSD-compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking for a thread-safe mkdir -p... /bin/mkdir -p checking for gawk... gawk checking whether make sets $(MAKE)... yes checking whether to enable maintainer-specific portions of Makefiles... no checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for style of include used by make... GNU checking dependency style of gcc... gcc3 checking -Wall is a valid compile option... yes checking for odbc_config... no configure: error: odbc_config not found (required for unixODBC build) you are missing a ODBC packages http://www.unixodbc.org/ http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/installing_configuring_odbc.html Regards Pavel Regards Manmohan. K On Thu, Oct 23, 2014 at 2:41 PM, Миша Тюрин tmih...@bk.ru wrote: http://multicorn.org/ another cool stuff to generalize interconnections Wed, 22 Oct 2014 22:34:06 -0700 от Postgres India pgbugin...@gmail.com: Thanks a lot Remi, Merlin and Pavel...I will give it a try. On Wed, Oct 22, 2014 at 6:36 AM, Merlin Moncure mmonc...@gmail.com https://e.mail.ru/compose/?mailto=mailto%3ammonc...@gmail.com wrote: On Wed, Oct 22, 2014 at 5:56 AM, Pavel Stehule pavel.steh...@gmail.com https://e.mail.ru/compose/?mailto=mailto%3apavel.steh...@gmail.com wrote: Hi try to use a Perl implementation https://github.com/davidfetter/DBI-Link There is a DBD driver for DB2 http://search.cpan.org/dist/DBD-DB2/DB2.pod Or you can use a mentioned fdw wrapper - there is ODBC wrapper https://wiki.postgresql.org/wiki/Foreign_data_wrappers#odbc_fdw and you can access DB2 via generic ODBC DB2 driver jdbc-fdw should work too. https://github.com/atris/JDBC_FDW merlin
[GENERAL] PostgreSQL on tablet grade SSD ?
Hi ! Can anyone share any experiences with running PostgreSQL on a tablet ? (Surface Pro 3, ASUS Transformer) (or point to things I should read, searching the web didn't turn up anything that seemed relevant to *tablets*) Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Unexpected planner behavior with *_pattern_ops index matching
Fabio Ugo Venchiarutti fa...@vuole.me writes: As you can see, CONCAT()'s output isn't deemed suitable for an index scan. The same happens for all type-compatible STABLE functions i tried. Conversion of the pattern to an index qualification requires that the pattern be a plan-time constant. STABLE functions, by definition, are not that. 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] Appending new data to existing field of Json data type
On 10/29/2014 11:26 PM, VENKTESH GUTTEDAR wrote: @Adrian Klaver, Thanks for this idea but still m really confused with how to update the json filed in the DB. is there any way to update the json field in the DB through view.? Sure, how you would normally update a value. Assuming id is unique: id2 = ExampleTable.objects.get(id=2) id2.example_list You now have the example_list and you can do what you want with it. What that is depends on what field type you have declared example_list in your model? That is why I suggested the jsonfield in a previous post, it automatically converts Python data structures into JSON and the reverse. Then: id2.save() In your Django project I would crank up: python manage.py shell and work with the model directly to see what is happening. -- Regards : Venktesh Guttedar. -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Appending new data to existing field of Json data type
On 10/29/2014 11:26 PM, VENKTESH GUTTEDAR wrote:
@Adrian Klaver, Thanks for this idea but still m really confused with
how to update the json filed in the DB. is there any way to update the
json field in the DB through view.?
Realized I should have shown at least one concrete example so, assuming
you are dealing with Python data structures where:
id2.example_list = {abc : [ { a:b,c:d } ] }
then
id2.example_list[abc].append({ e:f, g:h })
id2.example_list
{'abc': [{'a': 'b', 'c': 'd'}, {'e': 'f', 'g': 'h'}]}
then
Regards :
Venktesh Guttedar.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
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] syntax error while using pg_dump in Postgres 8.4
CrabbeS wrote I am trying to copy a table form one database to another by using this command in the SQL-query builder: pg_dump TA2000 -t from Forest -c -s | psql -h localhost postgis; pg_dump is a standalone application (i.e., something you run in a shell/command-line) and not an SQL command. Pipes (the | in your string) are also shell/command-line only and cannot be used where SQL is expected. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/syntax-error-while-using-pg-dump-in-Postgres-8-4-tp5825054p5825056.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Appending new data to existing field of Json data type
Thanks Adrian Klaver, Its really helped me to solve my problem, ya i have
example_list = jsonfield.JSONField() in models. Apologies i did not mention
that. but anyways thank again.
On Thu, Oct 30, 2014 at 8:09 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 10/29/2014 11:26 PM, VENKTESH GUTTEDAR wrote:
@Adrian Klaver, Thanks for this idea but still m really confused with
how to update the json filed in the DB. is there any way to update the
json field in the DB through view.?
Realized I should have shown at least one concrete example so, assuming
you are dealing with Python data structures where:
id2.example_list = {abc : [ { a:b,c:d } ] }
then
id2.example_list[abc].append({ e:f, g:h })
id2.example_list
{'abc': [{'a': 'b', 'c': 'd'}, {'e': 'f', 'g': 'h'}]}
then
Regards :
Venktesh Guttedar.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Regards :
Venktesh Guttedar.
Re: [GENERAL] syntax error while using pg_dump in Postgres 8.4
The psql console still operates mainly at the SQL level of input. You need to use your OS's shell to run pg_dump. Both psql and pg_dump are the same kind of application and are run in a similar fashion. David J. On Thu, Oct 30, 2014 at 11:13 AM, CrabbeS [via PostgreSQL] ml-node+s1045698n5825071...@n5.nabble.com wrote: ok That helps... I am now trying this in psql console- think this should work... (still getting some errors- but will try to figure it out) Thank you very much ! Sarah -- If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/syntax-error-while-using-pg-dump-in-Postgres-8-4-tp5825054p5825071.html To unsubscribe from syntax error while using pg_dump in Postgres 8.4, click here http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5825054code=ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb218NTgyNTA1NHwtMzI2NTA0MzIx . NAML http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml -- View this message in context: http://postgresql.1045698.n5.nabble.com/syntax-error-while-using-pg-dump-in-Postgres-8-4-tp5825054p5825073.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Estimating WAL usage during pg_basebackup
I need to get an idea of how much WAL space will be required during a long (many hours) pg_basebackup over a relatively slow network connection. This is for a server that's not yet running PITR / streaming. Any thoughts? * mike.blackw...@rrd.com*
[GENERAL] Planet Postgres
Hi, I don't know if this is the right mailing list for my question but I have already emailed pla...@postgresql.org without any response. What is the correct way to get a blog listed on planet postgres? The documented way does not work. I registered my blog on Monday. It still is in Not approved yet state. Yesterday I asked planet@... no response. So, what did I wrong? Thanks, Torsten -- 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] Planet Postgres
On 10/30/2014 12:02 PM, Torsten Förtsch wrote: Hi, I don't know if this is the right mailing list for my question but I have already emailed pla...@postgresql.org without any response. What is the correct way to get a blog listed on planet postgres? The documented way does not work. I registered my blog on Monday. It still is in Not approved yet state. Yesterday I asked planet@... no response. So, what did I wrong? Probably nothing, I would say you are waiting on step 4 from below to happen. http://planet.postgresql.org/add.html Thanks, Torsten -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Planet Postgres
* Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 10/30/2014 12:02 PM, Torsten Förtsch wrote: I don't know if this is the right mailing list for my question but I have already emailed pla...@postgresql.org without any response. That's the correct place to ask. -general is not. So, what did I wrong? Probably nothing, I would say you are waiting on step 4 from below to happen. http://planet.postgresql.org/add.html Indeed. The process is not automated and the moderators get to the blog approval when they get a chance to. Admittedly, it's usually faster, but it'll get done. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Unexpected planner behavior with *_pattern_ops index matching
Conversion of the pattern to an index qualification requires that the pattern be a plan-time constant. STABLE functions, by definition, are not that. Thank you very much I guess it is correct to assume that the same applies to regular expressions stored in pl/pgsql variables/arguments then, as they're inspected after the plan is cached? Is dynamic SQL the recommended/only way around this? Regards Fabio Ugo Venchiarutti -- 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] Unexpected planner behavior with *_pattern_ops index matching
Fabio Ugo Venchiarutti fa...@vuole.me writes: Conversion of the pattern to an index qualification requires that the pattern be a plan-time constant. STABLE functions, by definition, are not that. I guess it is correct to assume that the same applies to regular expressions stored in pl/pgsql variables/arguments then, as they're inspected after the plan is cached? Not necessarily. Recent PG versions will generate custom plans (ie, plans for the query with parameter values substituted as constants) if that consistently offers a significant win over the generic plan. Which it would as long as the parameter value always reduces to a reasonably long left-anchored pattern. 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] DBlink, postgres to DB2
Hi, On Thu, 2014-10-30 at 00:47 -0700, Postgres India wrote: configure: error: odbc_config not found (required for unixODBC build) I think you need to install unixODBC-devel rpm via yast. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [GENERAL] DBlink, postgres to DB2
On 10/22/2014 06:45 PM, Postgres India wrote: Hi, I am trying to connect DB2 from postgres using dblink, is there any configuration required at DB2 and postgres server. You'll need dbi-link (which uses Perl's DBI drivers) or a foreign data wrapper. dblink is for connections between PostgreSQL servers. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Estimating WAL usage during pg_basebackup
This is hard to tell, but You can get some estimation. 1. You can have WAL rate estimation from pg_xlog/ dir, i.e. How many WAL generated per minutes 2. How long this pg_basebackup will last. Lets say for 3 hours. Then You can multiple values in #1 and #2 to get rough estimation. Hope this would help. On Fri, Oct 31, 2014 at 1:52 AM, Mike Blackwell mike.blackw...@rrd.com wrote: I need to get an idea of how much WAL space will be required during a long (many hours) pg_basebackup over a relatively slow network connection. This is for a server that's not yet running PITR / streaming. Any thoughts? * mike.blackw...@rrd.com* -- Regards, Soni Maula Harriz
