[GENERAL] ecpg question
I wanted to ask this on here before going any further.
I've got functions that return rowsets, which from the psql shell require
me to write selects with AS TBL( ... ) to define the return types.
When I try to put a query together in ECPG to get values from these
functions, I discovered some nasty ecpg behavior.
EXEC SQL SELECT * FROM foo() AS TBL( c int );
compiles properly (?) to
{ ECPGdo(__LINE__, 0, 1, NULL, "select * from foo () as TBL ( c int )
", ECPGt_EOIT, ECPGt_EORT);}
however
EXEC SQL SELECT * FROM foo() AS TBL( c int, i int );
or any other query with multiple columns to a TBL description causes a
segfault in ecpg. It also concerns me that all other symbols are
dropped to lowercase, while TBL is still uppercase. Leads me to
believe that TBL isn't getting parsed. This was tested against cvs pgsql,
and the backtrace from the segfaults are consistent and appear below.
Program received signal SIGSEGV, Segmentation fault.
in strlen () from /usr/lib/libc.so.1
(gdb) bt
#0 in strlen () from /usr/lib/libc.so.1
#1 in cat2_str (str1=0xa5750 "c int ",
str2=0x2c ) at preproc.y:80
#2 in cat_str (count=3) at preproc.y:103
#3 in _end ()
#4 in main (argc=2, argv=0x) at ecpg.c:395
I tried picking the rules apart that call cat_str in this case, but I
can't really figure out where in preproc.y it gets called and screws that
second var argument to cat_str up. Hopefully someone has dealt with this
before and can tell me why what I'm doing is wrong, or that I should send
this on to -hackers.
Thanks,
-George
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] CHECK versus a Table for an enumeration
Hello Melanie
If your set of items will by static and small, then you can use CHECK
constraint. I use it for five, items itemes.
You can write more simple this constraint
...
user_type TEXT NOT NULL CHECK (user_type IN ('Root','Admin','Standard')),
regards
Pavel Stehule
On Mon, 22 Dec 2003, Melanie Bergeron wrote:
> Hi all!
>
> I want to know what's better between these 2 solutions :
>
> CREATE TABLE user (
> ...
> user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
> user_type = 'Standard'));
>
> or the following :
>
> CREATE TABLE user_type(
> user_type_id integer PRIMARY KEY,
> user_type_desc text);
>
> CREATE TABLE user (
> ...
> user_type_id integer,
> CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
> user(user_type_id));
>
> I am really confused so I'll wait for your advices.
>
> Thanks,
>
> Melanie
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Solved: questions about tsearch2 (for czech language)
Oleg
You has true. After restart of postmaster all works fine.
tsearch2=# select to_tsvector('default_czech','Jmenuji se Pavel Stěhule');
to_tsvector
'pavel':3 'stěhule':4 'jmenovat':1
Thank You very much
Pavel Stehule
On Mon, 22 Dec 2003, Oleg Bartunov wrote:
> Pavel,
>
> did you restart psql session after modifying tsearch2 configuration ?
> btw, there is czech dictionary available from
> http://lingucomponent.openoffice.org/download_dictionary.html
> We have utility to convert myspell dicts to ispell one. It's included
> in 7.5 development. Patch for 7.4 could be downloaded from
> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
>
> Also, historically, we use openfts mailing list for discussion of
> tsearch2.
>
> Oleg
> On Mon, 22 Dec 2003, Pavel Stehule wrote:
>
> > > > result. Why? Have I problem with my configuration?
> > >
> > > did you specify stop words in dictionaries configuration ?
> > >
> > > select * from pg_ts_dict;
> > >
> > tsearch2=# select * from pg_ts_dict where dict_name ='cz_ispell';
> > -[ RECORD 1
> > ]---+---
> > dict_name | cz_ispell
> > dict_init | 173405
> > dict_initoption |
> > DictFile="/usr/lib/ispell/czech",AffFile="/usr/lib/ispell/czech.aff",StopFile="/usr/local/pgsql/share/contrib/czech.stop"
> > dict_lexize | 173406
> > dict_comment|
> >
> > [EMAIL PROTECTED] root]$ cat /usr/local/pgsql/share/contrib/czech.stop|grep -e
> > "^[sv]."
> > se
> > sem
> > si
> > svůj
> > ve
> > vám
> > váš
> > viz
> > vy
> >
> > > >
> > > > 2. I use small czech dictionary. I need don't erase words which aren't in
> > > > dictionary (in my sample Stěhule). Can I set it somewhere? I tryed add
> > > > simple dict into cfg map, but witout sucess
> > > >
> > >
> > > Example, please ! What do you mean 'erase words' ?
> > >
> > >
> > > > tsearch2=# select * from ts_debug('jmenuji se Pavel Stěhule a bydlím ve
> > > > Skalici.'); ts_name| tok_type | description | token |
> > > > dict_name | tsvector
> > > > ---+--+-+-++---
> > > > default_czech | word | Word| Stěhule | {cz_ispell,simple} |
> > > > default_czech | lword| Latin word | a | {cz_ispell,simple} |
> > > > default_czech | word | Word| bydlím | {cz_ispell,simple} |
> > > > 'bydlet'
> > > >
> > > >
> >
> > If tsearch didn't find word in dictionary, then erase this from result.
> > True? My surname, fo example isn't in dictionary, but I wont save this
> > word in result (tsvector).
> >
> > I use
> >
> > tsearch2=# select version();
> > version
> > ---
> > PostgreSQL 7.4RC2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3
> > 20030715 (Red Hat Linux 3.3-14)
> >
> >
>
> Regards,
> Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] getting configure options?
On 23/12/2003 21:46 CSN wrote: Is is possible to find out what options (prefix, bindir, etc.) were specified to "configure" when Postgres was built? pg_config --configure HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] bug in query planning?
The queries are listed here for the referentially (yes that's a pun) challenged. Query 1: SELECT COUNT(message_id) FROM messages m LEFT JOIN accounts a ON m.account_id::bigint = a.account_id::bigint WHERE a.email = '[EMAIL PROTECTED]'; Query 2: SELECT COUNT(message_id) FROM accounts a LEFT JOIN messages m ON a.account_id::bigint = m.account_id::bigint WHERE a.email = '[EMAIL PROTECTED]'; Query 3: SELECT COUNT(message_id) FROM messages m, accounts a WHERE m.account_id::bigint = a.account_id::bigint AND a.email = '[EMAIL PROTECTED]'; From what I can see they are not the same query and therefore shouldn't use the same plan. The first query is saying go get all the messages (best done with a seq scan since there is no where to limit the results of the message table [using an index scan would just add the overhead of reading the pages for the index, the computational time to resolve the index entries, and turn the table access into a random sector read instead of sequential without actually limiting what gets returned]) match that with as many accounts as you can and return a row for all of the messages (note the LEFT JOIN). Next filter all of the results on the account email (which only eliminates 1100 messages out of 52000). Now count how many messages are left which should return 51419. The second query is saying get all of the accounts filter by email address (it can get this from the where this time) giving 1 row. Now match that to every message for this account_id and return at least one row even if there are no messages for this account (note again the LEFT JOIN) (which uses the index scan because it expects the index selectivity to be a approximately 1/4 of the full table [it's wrong]). Now count how many messages I have which returns 51419. The third query is saying give me all of the messages for the accounts where my email = '[EMAIL PROTECTED]' and I don't care where you start from. The optimizer, after going through consideration of various possible plans, is then smart enough to realize the email = 'blah' is indexed and it's selectivity is 1 row which means that we now return to the situation in query 2 with one small change if there are no messages for the account in question you would get no row returned, leading to a more efficient aggregation step. Steven D.Arnold wrote: On Dec 21, 2003, at 11:47 PM, Tom Lane wrote: "Steven D.Arnold" <[EMAIL PROTECTED]> writes: Query (2) below is the same query, but we reverse the order of the tables. It's obviously not quite the same query semantically, even though in my case it should always produce the same result. You are correct the queries produce the same results, but they are telling the planner to do completely different things. The query doesn't show it bu if the behavior you are desiring happened in postgres (unless show the relational algebra that makes it work), I would have to start looking for a new database (that's a disturbing thought). Since it is in fact not the same query, I'm unclear on why you expect it to produce the same plan. What I expect is for both queries to use the index on the messages table! Why is it not doing that? Because of the table ordering and the left join in 7.3.x Because of the left join in 7.4 FWIW, I believe that 7.4 will recognize that (1) and (3) are semantically equivalent. I will try 7.4 and report back. I don't believe the optimiser (in any database that cares about giving you the correct results) can determine that a non-constrained primary table in a left join can be rewritten as either of your other two queries (but there are smarter people than me working on Postgres, so I could be wrong). steve My suggestion would be to place the more selective table first in a JOIN, and get rid of the LEFT JOIN's unless that's exactly what you want. For more information about the different JOIN methods RTFM. I would also suggest that you might want to tune your random page cost toward 1, because obviously random access is being over estimated for your hardware. (You might just want to look at tuning your parameters in general.) And in the future you should run a query at least one extra time to note the different caching makes (the second run for an explain analyze is usually quite different than the first for tables of this size). DeJuan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Mirrors that don't suck.
> Hey list, I'm just wondering if anyone can point me in the direction of a mirror > that doesn't suck. At least in the US, I think I should be pretty decent (ftp3.us.postgresql.org). I've rate limited down to 6M/s but that should be enough for anyone. I rsync every 4 hours. If anyone can't get to the site of it's slow, please complain to me, I would love to know. - Brandon c: 917-697-8665h: 201-435-6226 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How to hide database structure
On Mon, Dec 22, 2003 at 09:10:43 -0800,
Michael Gill <[EMAIL PROTECTED]> wrote:
> How can I hide all details from users regarding data and structure,
> limiting access to data through functions I create?
>
> I don't want a user to be able to see the structure of a table with \d
> {tablename} in psql.
Don't give them any direct access to the tables and use a security definer
function to give them indirect access.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to hide database structure
On Tue, Dec 23, 2003 at 22:04:56 -0600,
Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Mon, Dec 22, 2003 at 09:10:43 -0800,
> Michael Gill <[EMAIL PROTECTED]> wrote:
> > How can I hide all details from users regarding data and structure,
> > limiting access to data through functions I create?
> >
> > I don't want a user to be able to see the structure of a table with \d
> > {tablename} in psql.
>
> Don't give them any direct access to the tables and use a security definer
> function to give them indirect access.
I made a mistake. The above only limits access to the data. You can't
limit access to the structure while allowing direct access to the database.
You will need to create a proxy application/server if you want to do this.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] bug in query planning?
DeJuan Jackson <[EMAIL PROTECTED]> writes: > Query 1: > SELECT COUNT(message_id) > FROM messages m > LEFT JOIN accounts a >ON m.account_id::bigint = a.account_id::bigint > WHERE a.email = '[EMAIL PROTECTED]'; > Query 2: > SELECT COUNT(message_id) > FROM accounts a > LEFT JOIN messages m >ON a.account_id::bigint = m.account_id::bigint > WHERE a.email = '[EMAIL PROTECTED]'; > Query 3: > SELECT COUNT(message_id) > FROM messages m, accounts a > WHERE m.account_id::bigint = a.account_id::bigint >AND a.email = '[EMAIL PROTECTED]'; > From what I can see they are not the same query and therefore shouldn't > use the same plan. Actually, queries 1 and 3 are equivalent, and I believe PG 7.4 will recognize them as such. The reason is that the WHERE clause "a.email = 'something'" cannot succeed when a.email is NULL; therefore, there is no point in the JOIN being a LEFT JOIN --- any null-extended rows added by the left join will be thrown away again by the WHERE clause. We may as well reduce the LEFT JOIN to a plain inner JOIN, whereupon query 1 is obviously the same as query 3. PG 7.4's optimizer can make exactly this sequence of deductions. The bit of knowledge it needs for this is that the '=' operator involved is STRICT, ie, yields NULL for NULL input. All the standard '=' operators are strict and are so marked in the catalogs. (If you are defining a user-defined type, don't forget to mark your operators strict where applicable.) I believe that query 2 is really equivalent to the others as well, but proving it is more subtle. The reason is that COUNT(message_id) does not count rows where message_id is NULL, and so any null-extended rows added by the LEFT JOIN won't be counted, and so we might as well reduce the LEFT JOIN to a plain inner JOIN. PG's optimizer will not recognize this, however. Possibly it could if anyone wanted to figure out how. Right now we make very few assumptions about the behavior of aggregate functions, but I think you could prove that this is safe based on the behavior of nodeAgg.c for strict transition functions. Next question is whether the case would come up often enough to be worth testing for ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Where do I get Windows libpq and header files?
Hello I have postgresql latest version running on RH 9. I want to write a windows based application to interact with postgresql server. For that I plan to use libpq i.e. using C. I can work with the linux version but try as I might I am not able to locate a source for compiled ( static ) version of libpq and its header files for Windows. Where can I get it? Thanks in advance. Karam __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
