Re: [GENERAL] [fulltext]Gin index full scan
On Mon, May 11, 2009 at 8:04 PM, esemba wrote:
>
> I've a table with tsvector column named meta_vector. Over this column there
> is a gin index. When I execute query like:
> select id from theses where meta_vector @@@ to_tsquery('cs', 'foo | (!bar)')
>
> I get an errror message: query requires full scan, which is not supported by
> GIN indexes.
>
> The weird thing is, that when I drop the index, or disable index scans, it
> works. Why can't the planner just use full scans instead of index scans on
> such the queries? Thanks for help.
You can search the archives for the last time this was brought up.
Apparently, it isn't an easy fix. People hoped to have it addressed
for 8.4 but I don't know if it made it. Unfortunately, this makes GIN
indexes unusable for many applications such as text searching using
arbitrary user queries. GIST indexes work, but perform worse for
reads.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this a bug or a feature? Column visibility in subquery from outer query
postgres=# create table public.ps_test_x (x1 oid, x2 oid, x3 oid); CREATE TABLE postgres=# create table public.ps_test_y (y1 oid, y2 oid, y3 oid); CREATE TABLE postgres=# explain select * from public.ps_test_x where x1 in (select x1 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x2 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x3 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x4 from public.ps_test_y); ERROR: column "x4" does not exist LINE 1: ...elect * from public.ps_test_x where x1 in (select x4 from pu... ^ postgres=# explain select * from public.ps_test_x where x1 in (select y1 from public.ps_test_y); QUERY PLAN --- Hash Join (cost=36.62..88.66 rows=1770 width=12) Hash Cond: (ps_test_x.x1 = ps_test_y.y1) -> Seq Scan on ps_test_x (cost=0.00..27.70 rows=1770 width=12) -> Hash (cost=34.12..34.12 rows=200 width=4) -> HashAggregate (cost=32.12..34.12 rows=200 width=4) -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=4) (6 rows) I just want to point out that the sub-query is using a column from the outer query (eg. x1) without an alias from the table in the outer query. This can lead to a confusion when, for example: delete from table1 where foreign_id in (select foreign_id from table2) -- ! table2 does not have the foreign_id column ! This would do a table scan on table1 and delete all its rows. Why isn't it like: delete from table1 where foreign_id in (select table1.foreign_id from table2) where you must specify the outer query's table reference inside the subquery. I suspect the original intent was to use the outer query columns in some processing inside the subquery, which is valid. I'm just wondering why an explicit reference isn't required to distinguish the column. For convenience, or part of the SQL spec? Cheers! --Paolo Saul
Re: [GENERAL] Daylight saving time question
Bayless Kirtley wrote: How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. it uses your client's specified local time zone to determine whether or not DST is in effect. SET TIME ZONE 'America/New York'; or SET TIME ZONE 'PST8PDT'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Daylight saving time question
How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. TIA Bayless
Re: [GENERAL] Excel and pg
Sergio- the idea to accomodate mandarin audience is a good one..china represents a vital growing economy and will appreciate the fact you are writing in their lang There are about 12 different ways of accomplishing this featureset in J2EE TC would be the lightest implementation GF or WL would be the more heavyweight J2EE AppServer offerings Resin and Websphere would be middle category offering You can make this as simple as possible..a few jsp pages and/or complicated via SSO Portal which governs accesses to resources based on supplied Signon principal all of your resources (pages) would accomodate language of choice English, Mandarin, German French or Italian or Hungarian..mandarin is far tougher conversion as most ideas are represented by graphs vs typical Indo-European language..a good point is once you achieve 1 far-eastern lang it is a far easier conversion to go into Japanese Korean as CJK are generally considered to use a common base character set Excel/VB is specific to windows GUI desktop and has little flexibility for conversion to webapp which is how 95% of apps are coded now..at least from what i am currentlt seeing Please read the link that I sent you on Unicode to gain an understanding on how Unicode is implemented on Postgres Server and Client Martin Gainty __ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Mon, 18 May 2009 09:14:41 +0800 > From: cr...@postnewspapers.com.au > To: m...@webthatworks.it > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Excel and pg > > Ivan Sergio Borgonovo wrote: > > > I'd like to know if: > > - it is possible to "load" in an Excel sheet a table (view, query > > result) coming from postgresql and to use those data to do further > > computation/presentation work on Excel? > > Certainly. You can do it through the ODBC interface via VB, and I think > Excel also has some kind of "data browser" that lets the user pull data > from ODBC-accessed databases interactively. > > Beware, though. Excel has funny ideas about dates and has some baked-in > bugs in some of its functions. It doesn't know about or respect the > foreign key relationships and constraints in the database, either. > > If you really must pull data into Excel, consider giving users an > account in PostgreSQL that _ONLY_ has access to read-only views of the > data. Those views should denormalize the data significantly and > otherwise make it as Excel-friendly as possible. Pull the data in using > a Visual Basic script that "protects" the data as soon as it's been > placed on the sheets, so the user can't accidentally change it, just > reference it. > > > I think the rough path > > should be use ODBC (OleDB?) Do I have to install anything more > > other than postgresql? > > Yes. The Pg ODBC driver. > > > - can postgresql load data from an Excel sheet? Or Excel write data > > to postgresql from an excel sheet? dblink? > > The easiest way is via CSV. You could probably also do it with some > Visual Basic running in Excel that pushes the data via ODBC. > > If you're going to even vaguely consider putting data from a > user-modifiable spreadsheet back in the DB, make sure to protect every > cell the user isn't explicitly meant to be able to modify. > > > - am I going to incur in any localisation problem if the Windows > > stuff is localised in Chinese? I see I can chose the "language to > > be used during installation". I'd prefer localization to be in > > English but still let people that will use the front-end to use > > Chinese. What about the e
Re: [GENERAL] Excel and pg
Martin Gainty wrote: > There are about 12 different ways of accomplishing this featureset in J2EE > TC would be the lightest implementation For the unitiated: Tomcat (from the Apache Software Foundation) > GF or WL would be the more heavyweight J2EE AppServer offerings For the uninitiated: GlassFish (from Sun) and WebLogic (from BEA/Oracle) J2EE development isn't the shrieking nightmare it used to be with Enterprise Java Beans, since JPA 1.0 and implementations of it like Hibernate Annotations make things a _great_ deal less painful. You still need to "get" Java in a pretty solid way, and be willing to rethink the way you handle database access a bit. You're doing your database access via an object-model translation layer in an application server, accessing and manipulating persistent objects that back onto the real database. You have to define the persistence scheme by which these objects are stored and retrieved using tools like Hibernate or JPA 1.0 (Hibernate Annotations, Toplink, etc). You have to understand Java object persistence and migration, vaguely how the app servers work, how to work with JSP, etc. Many of the same tools are useful in a J2SE environment for rich client development using Swing, but that's a *lot* of work for a volunteer project. -- Craig Ringer -- 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] Excel and pg
Ivan Sergio Borgonovo wrote:
> I'd like to know if:
> - it is possible to "load" in an Excel sheet a table (view, query
> result) coming from postgresql and to use those data to do further
> computation/presentation work on Excel?
Certainly. You can do it through the ODBC interface via VB, and I think
Excel also has some kind of "data browser" that lets the user pull data
from ODBC-accessed databases interactively.
Beware, though. Excel has funny ideas about dates and has some baked-in
bugs in some of its functions. It doesn't know about or respect the
foreign key relationships and constraints in the database, either.
If you really must pull data into Excel, consider giving users an
account in PostgreSQL that _ONLY_ has access to read-only views of the
data. Those views should denormalize the data significantly and
otherwise make it as Excel-friendly as possible. Pull the data in using
a Visual Basic script that "protects" the data as soon as it's been
placed on the sheets, so the user can't accidentally change it, just
reference it.
> I think the rough path
> should be use ODBC (OleDB?) Do I have to install anything more
> other than postgresql?
Yes. The Pg ODBC driver.
> - can postgresql load data from an Excel sheet? Or Excel write data
> to postgresql from an excel sheet? dblink?
The easiest way is via CSV. You could probably also do it with some
Visual Basic running in Excel that pushes the data via ODBC.
If you're going to even vaguely consider putting data from a
user-modifiable spreadsheet back in the DB, make sure to protect every
cell the user isn't explicitly meant to be able to modify.
> - am I going to incur in any localisation problem if the Windows
> stuff is localised in Chinese? I see I can chose the "language to
> be used during installation". I'd prefer localization to be in
> English but still let people that will use the front-end to use
> Chinese. What about the encoding (client/server)?
Use UTF-8 for the client and server encodings. Excel should convert that
to/from UTF-16 ("Unicode") just fine if you use the Unicode ODBC driver
for PostgreSQL.
> - are there tools to make backup/restore very easy even for
> "point&click" kind of users?
Make a batch file / script that runs pg_dump. Alternately, use PgAdmin III.
> - anything that a non "desktop" oriented guy like me have to realise
> before promising to put up something that will have to be used by
> "desktop/GUI" people?
You have no idea how much pain you are letting yourself into.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help
Hi, I am running Dspace 1.5.1, which is an institutional repository software. I am using postgresql-8.3 there. Here, i am facing the problem is: our server suddenly went down and after searching, i found out that, the OS crashed. So, i had to re-install my OS again. But, the problem is, I couldnot take the dump file for my postgresql database. Now, i have the raw data, that means, my datas are in a disk. Is it possible to restore those data here in new server? -- Best, Zico
[GENERAL] Excel and pg
I've to deal with a volunteer pet project and I wouldn't like to get crazy fighting with the idiosyncrasies of Access but still I've no time to build up an interface that will be enough user friendly to overcome the resistance of something new. So I thought just to use Excel 2003 as the front-end to postgresql, everything on Windows XP. I'm not any more (if I have ever been) comfortable with MS Office stuff and Windows. I know using postgresql is like using an elephant to fight a microbe here, but still since I'll have to write the business logic and I don't want to spend hours understanding why I can't make a join or what is the equivalent of coalesce etc... I'd like to know if I'm getting into more trouble just to set the things up. I'd like to know if: - it is possible to "load" in an Excel sheet a table (view, query result) coming from postgresql and to use those data to do further computation/presentation work on Excel? I think the rough path should be use ODBC (OleDB?) Do I have to install anything more other than postgresql? - can postgresql load data from an Excel sheet? Or Excel write data to postgresql from an excel sheet? dblink? - am I going to incur in any localisation problem if the Windows stuff is localised in Chinese? I see I can chose the "language to be used during installation". I'd prefer localization to be in English but still let people that will use the front-end to use Chinese. What about the encoding (client/server)? - are there tools to make backup/restore very easy even for "point&click" kind of users? - anything that a non "desktop" oriented guy like me have to realise before promising to put up something that will have to be used by "desktop/GUI" people? I can't think about anything else other than backup they will have to deal with once they see their data in Excel and they can backup/restore easily. thanks BTW I saw a lot of nice things on the pg package for Windows... especially for debugging that I'm not sure I can find for Linux or can be as easily installed as with Stack Builder: - debugger - Tuning Wizard - replication solution (I wonder if it is easier to have a working solution with aptitude or Stack Builder) -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] subscribe
subscribe end
