Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans

2010-05-21 Thread Kevin Flanagan
Ah - you mentioning index definitions has suddenly made it clearer just what
that error message might mean. The source_lang_code and target_lang_code
columns didn't yet each have an index. If I create an index for either one
of them, the error then goes away, I'm guessing because the query processor
can use one index or other to filter table rows before applying the
full-text filter, rather than applying the full-text filter first then
applying the ='code' filters to the results - which must be what the error
means you can't do.

Strange, though - if I change the ='code' terms to use LIKE, it works ...
so it obviously can be done without adding another index. 

Still, those two columns both needed an index anyway, and everything then
works just fine, so I shan't worry about that :)

Thank you very much.

Kevin.


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: 20 May 2010 22:15
To: Kevin Flanagan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans 

Kevin Flanagan kevi...@linkprior.com writes:
 Why would adding target_lang_code='en' cause this error?

Hard to tell without seeing the index definitions for this table.
Also could we see the EXPLAIN plans for both queries?  (If possible
... I'm not sure whether you'd get this error just from EXPLAINing
the problem query.)

 Environment: PostgreSQL 8.4 on Windows (installed with one-click
installer),

8.4.what-exactly?

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] ERROR: GIN indexes do not support whole-index scans

2010-05-20 Thread Kevin Flanagan
Could anyone advise as to how to avoid this error? I'll describe the table
and query below.

 

The database contains a table 'tinytm_segments', which has two text columns,
'source_text' and 'target_text'. These are used to store sentences and their
translations. The language of the text is specified with typical
two-character identifiers ('en', 'fr' etc.) stored in two further columns,
'source_lang_code' and 'target_lang_code'. Translation in either direction
can be stored, so for a given row, source_text may contain English and
target_text French (with the corresponding values in source_lang_code and
target_lang_code), or the other way round.

 

The application needs to search for (say) French sentences containing a
given substring and retrieve any English translation found (or whatever
other language combination and direction). To perform better with large
datasets, full text indices are defined, such as these:

 

-- Index English text

CREATE INDEX tu_target_text_en_idx ON tinytm_segments USING
gin(to_tsvector('english', target_text)) where target_lang_code = 'en';

CREATE INDEX tu_source_text_en_idx ON tinytm_segments USING
gin(to_tsvector('english', source_text)) where source_lang_code = 'en';

 

-- Index French text

CREATE INDEX tu_source_text_fr_idx ON tinytm_segments USING
gin(to_tsvector('french', source_text)) where source_lang_code = 'fr';

CREATE INDEX tu_target_text_fr_idx ON tinytm_segments USING
gin(to_tsvector('french', target_text)) where target_lang_code = 'fr';

 

To retrieve (say) sentences that have been translated from French, where the
French contains a given substring, a query like this can then be issued:

 

SELECT * FROM  tinytm_segments WHERE

source_lang_code='fr'  AND 

to_tsvector('french', source_text) @@ plainto_tsquery('french', 'rien du
tout') AND lower(source_text) LIKE '%rien du tout%'

 

However, that will return sentences translated into whatever language. The
error occurs when trying to retrieve only sentences translated from French
into English, using a query like this:

 

SELECT * FROM  tinytm_segments WHERE

source_lang_code='fr'  AND 

to_tsvector('french', source_text) @@ plainto_tsquery('french', 'rien du
tout') AND lower(source_text) LIKE '%rien du tout%'

 AND target_lang_code='en'

 

Why would adding target_lang_code='en' cause this error?

 

Environment: PostgreSQL 8.4 on Windows (installed with one-click installer),
default text search config used.

 

Thanks in advance for any information.

 

Kevin.

 



Re: [HACKERS] Access violation from palloc, Visual Studio 2005, C-language function

2010-03-10 Thread Kevin Flanagan
Aha. I'd read that the build process for the contrib modules involved
generating a .DEF file for the necessary exports. I had the impression that
defining BUILDING_DLL was an alternative, addressing (part) of the issue
(that is, PG_FUNCTION_INFO_V1 declares functions as 'extern PGDLLIMPORT',
and if you define BUILDING_DLL, then PGDLLIMPORT is defined as ' __declspec
(dllexport)'). But you're quite right, if I take out the BUILDING_DLL
definition, and put the __declspec (dllexport) stuff in piecemeal, the
access violation goes away. Thank goodness.

Thanks, that really helped me out.

Kevin.



-Original Message-
From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
Sent: 10 March 2010 18:51
To: Kevin Flanagan
Cc: 'PostgreSQL-development'
Subject: Re: [HACKERS] Access violation from palloc, Visual Studio 2005,
C-language function 

Kevin Flanagan kevi...@linkprior.com writes:
 Hard to tell without seeing the actual code and a stack trace, but I'd
 bet that you haven't fully resolved the build process problems you
 mentioned earlier.  

 I've attached a zip of the (tiny) project, and a text file with the
contents
 of the module containing the C-language functions. The only difference
from
 sample code is that (as pointed out by Takahiro Itagaki in his post here
of
 8th March) the function implementations need decorating with
 __declspec(dllexport).

Mph.  I don't actually believe that, nor do I believe the #define
BUILDING_DLL you put in, because neither of those are needed in any of
our contrib modules.  What I suspect at this point is that the reference
to CurrentMemoryContext in the palloc() macro is being bollixed by
having the wrong value for BUILDING_DLL.  However, not having a Windows
build environment to experiment with, I'll have to defer to somebody
with more experience in that.

(I wonder BTW if we should rename BUILDING_DLL, because it seems a bit
misnamed.  AIUI it's supposed to be set while building the core backend,
not while building loadable modules.)

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


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


[HACKERS] Access violation from palloc, Visual Studio 2005, C-language function

2010-03-09 Thread Kevin Flanagan
Environment: Windows Vista, PostgreSQL 8.4 (1-click installer), Visual
Studio 2005 sp1.

I have a bare-bones DLL built as per the above, compiling the 'add_one' and
'copytext' samples found at
http://www.postgresql.org/docs/8.4/interactive/xfunc-c.html (version 1
calling convention), compiled as 'C'. I can use 'add_one' just fine from
within SQL, but if I use 'copytext', an access violation occurs as soon as
palloc() is called.

Could anyone suggest what the problem might be?

Failing that, are there any other (creative?) ways to return strings from a
C-language function without using palloc?

Thanks in advance for any leads

Kevin.



-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Kevin Flanagan
Ok, re building with the win32 configuration ... that sounds like just the 
thing I should know about. All I've done is downloaded and installed the 
1-click installer for Windows from 
http://www.enterprisedb.com/products/pgdownload.do#windows ... so while I'm 
sure it knows it's running on Win32, is there some other configuration change I 
should make for dev purposes to indicate that it's the win32 configuration? 
Or does building with the win32 configuration refer to those who are building 
the server from source, or something?

Thanks

Kevin

-Original Message-
From: Craig Ringer [mailto:cr...@postnewspapers.com.au] 
Sent: 05 March 2010 04:02
To: Kevin Flanagan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?

Kevin Flanagan wrote:

 the compiler
 complained about various missing include files, starting with
 ‘libintl.h’. Having read the post at
 http://archives.postgresql.org/pgsql-general/2009-03/msg00332.php I
 created an empty libint.h in an include dir

NFI why Pg for win32 doesn't bundle a copy of the libintl it was built
against. I should poke the EDB guys about it, actually.

 along with a bunch of other
 empty dummy files that were expected: netdb.h, pwd.h, netinet/in.h and
 arpa/inet.h.

Those I wouldn't expect to be included if you're building for win32.

Are you sure you're building with the win32 configuration?

 The code then compiles ok, but gives ‘inconsistent dll linkage’ on the
 line with PG_FUNCTION_INFO_V1 and the one with PG_MODULE_MAGIC.

This would suggest that the macros that insert appropriate
__declspec(dllimport) and __declspec(dllexport) attributes aren't being
triggered - so again, it makes me wonder if Pg knows it's building for
win32.

--
Craig Ringer


-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Kevin Flanagan
Ok, that got me on the right track, thanks. I think the key points for this 
build scenario are these:

1. you have to define the symbol BUILDING_DLL in your code before including 
postgres.h (as that then means PGDLLIMPORT gets defined right in 
pg_config_os.h). That makes the 'inconsistent dll linkage' warnings go away.
2. you have to have include\server\port\win32 in the include dirs list as well 
as include\server (as that provides a bunch of otherwise-missing headers such 
as netdb.h)

However, that still leaves one missing include file - libintl.h, which c.h 
tries to include because ENABLE_NLS is defined (and that seems to get defined 
as 1 in pg_config.h whether you like it or not). And in fact, it seems Rostic 
Sheykhet posted at http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html 
with the same problem (now that I know how to do it, I know what to Google for 
to, er, find out how to do it :) ). You can get round it by commenting out the 
include or creating a dummy libintl.h.

Just posting the results here in case they're relevant for anything.

Kevin.


-Original Message-
From: Craig Ringer [mailto:cr...@postnewspapers.com.au] 
Sent: 05 March 2010 10:05
To: Kevin Flanagan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?

Kevin Flanagan wrote:
 Ok, re building with the win32 configuration ... that sounds like just the 
 thing I should know about. All I've done is downloaded and installed the 
 1-click installer for Windows from 
 http://www.enterprisedb.com/products/pgdownload.do#windows ... so while I'm 
 sure it knows it's running on Win32, is there some other configuration change 
 I should make for dev purposes to indicate that it's the win32 
 configuration? Or does building with the win32 configuration refer to 
 those who are building the server from source, or something?

I wasn't too specific because it's been a while since I did any coding
against Pg on win32, and I couldn't remember exactly how it selected the
right code to use for a given platform - whether it was a macro that
must be defined, or what.

Having had a look at the sources: It's done by header search path. You
need to make sure that include/port/win32_msvc is on the header search
path as well as the main include/ directory.

I *think* port/win32 is for the MinGW win32 port and thus shouldn't be
included in the search path for msvc builds, but I'm not 100% sure of
that and a quick look doesn't reveal any documentation on the matter.

--
Craig Ringer


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


[HACKERS] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-04 Thread Kevin Flanagan
I have PostgreSQL 8.4 installed on Windows XP, and am using Visual Studio
2005 to write a C-Language function. I have the most basic hello-world type
example (just the 'add_one' function from
http://www.postgresql.org/docs/8.4/interactive/xfunc-c.html) in a DLL, set
to compile to C code rather than C++, and I can do CREATE FUNCTION on it
then use it successfully. However, I had to do some questionable hacks to
get it to build, so I'm guessing I've done something the wrong way. I've not
found anything categorical in the archives here, so would very much
appreciate anyone who can tell me if there's a key step I'm missing that
would make the hacks unnecessary. (For instance, I've come across a
requirement in previous versions to replace pg_config.h with
pg_config.h.win32, but that doesn't seem to apply to current versions -
maybe something else does.) I'm concerned the hacks might turn round and
bite me otherwise once I'm implementing something non-trivial.

 

Having added C:\Program Files\PostgreSQL\8.4\include\server to the include
directories to pick up postgres.h and fmgr.h, the compiler complained about
various missing include files, starting with 'libintl.h'. Having read the
post at http://archives.postgresql.org/pgsql-general/2009-03/msg00332.php I
created an empty libint.h in an include dir, along with a bunch of other
empty dummy files that were expected: netdb.h, pwd.h, netinet/in.h and
arpa/inet.h. 

 

The code then compiles ok, but gives 'inconsistent dll linkage' on the line
with PG_FUNCTION_INFO_V1 and the one with PG_MODULE_MAGIC.

 

So I'd like to ask:

* Is there some symbol I should be defining, or something, to avoid all
those dummy header files and make the corresponding Visual Studio headers
get picked up?

* Is there something I should be doing to avoid the 'inconsistent dll
linkage' warnings?

 

(The C-Language functions are going to end up using some in-proc
Windows-only components, so I'm hoping that building them using Visual
Studio will be the least painful way to get at those.)

 

Thanks in advance for any help

 

Kevin.