Re: [SQL] dump and schema
Tom Lane wrote: van Elsacker Frans <[EMAIL PROTECTED]> writes: All my tables (more than 100) have a field datumi of type date default today, as this example: CREATE TABLE tabel ( datumi date DEFAULT 'today', ); This does not work anymore in PG 7.4. You need something like datumi date DEFAULT CURRENT_DATE, regards, tom lane I think this won't get the same result he expects. As I know, the CURRENT_DATE will always give the current day, not the day you inserted the row. I think the now()::date seems to be a better way to translate the 'today' value. -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] failed to build any 5-way joins
Hi all, I got the error message reported in the subject and I could not find anything about it anywhere. I cannot get any explain plan for this query. I'm working on PosgreSQL 7.4-1, the latest cygwin distribution I can find. The query generating the error is: select *from info where parent_infoid is null and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_auth fa where userid = 8 and fa.groupid = uag1.groupid) and main_infoid in (select ic.infoid from info_category ic, category_auth ca, users_auth_groups uag2 where ic.categoryid = ca.categoryid and uag2.userid = 8 and uag2.groupid = ca.groupid and read_write = 'W') I already have a workaround, but I don't like it because it seems to me that it could be a little slower (executing the exists clause for every possible record got from the rest of the query, according to the explain plan results - and if I understood them well :) ): select *from info where parent_infoid is null and exists (select * from users_auth_groups uag1, field_auth fa where userid = 8 and fa.groupid = uag1.groupid and info.fieldtypeid = fa.fieldtypeid) and main_infoid in (select ic.infoid from info_category ic, category_auth ca, users_auth_groups uag2 where ic.categoryid = ca.categoryid and uag2.userid = 8 and uag2.groupid = ca.groupid and read_write = 'W') This seems to work, however (and I will keep it until I will find another solution - well, I know that, for example, I can do it using stored procedure, splitting the query in more steps, but this is not my first choice, at the moment, for reasons too long to explain here). The problem is: does anyone know what sort of error did I get and/or where can I find docs about it? I suppose (just from the error message) that the optimizer is trying to translate the first query in a join and it gets an error doing so. But, is this error related to some limits in PostgreSQL? Are there limits in joining tables? I could not find anything about this too (I just found a my-sql crash-me test report in which they tell that PostgreSQL - v. 7.1.1 - passed the test with 64+ tables). Thanks to you all for you help Bye Alessandro Depase
Re: [SQL] dump and schema
Olivier Hubaut wrote: > I think this won't get the same result he expects. As I know, the > CURRENT_DATE will always give the current day, not the day you > inserted the row. Your knowledge is incorrect. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Pgaccess problem
Hi All,
I am trying to install pgaccess on a redhat 7.2 linux server with
postgres7.4. Everything seems to be alright but when I go to run pgaccess I
get an error message saying
Application initialization failed: no display name and no $DISPLAY
environment v
ariable
Error in startup script: invalid command name "image"
while executing
"image create bitmap dnarw -data {
#define down_arrow_width 15
#define down_arrow_height 15
static char down_arrow_bits[] = {
0x00,0x80,0x00,0x80,0x0..."
(file "/usr/local/pgaccess/main.tcl" line 5)
Can anyone Offer any advice on this problem?
Thanks in advance,
Vijay
_
Stand out from the crowd. Make your own MMS cards. http://msn.migasia.cn/msn
Have some mobile masti!
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] failed to build any 5-way joins
Alessandro Depase wrote: > The query generating the error is: This is useless unless we know table schema, what data is in the tables, and what software version you use. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] dump and schema
Olivier Hubaut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> This does not work anymore in PG 7.4. You need something like >> datumi date DEFAULT CURRENT_DATE, > I think the now()::date seems to be a better way to translate the > 'today' value. There is no real difference. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] failed to build any 5-way joins
"Alessandro Depase" <[EMAIL PROTECTED]> writes: > I got the error message reported in the subject and I could not find anythi= > ng about it anywhere. It's a bug, but we can't do much about it unless you provide a reproducible example. In addition to the query itself, schemas for the tables involved would be needed (use pg_dump -s). Test that you can load the schema dump into an empty database, execute the problem query, and get the failure. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Pgaccess problem
On 16/12/2003 13:29 vijaykumar M wrote:
Hi All,
I am trying to install pgaccess on a redhat 7.2 linux server with
postgres7.4. Everything seems to be alright but when I go to run
pgaccess I get an error message saying
Application initialization failed: no display name and no $DISPLAY
environment v
ariable
Error in startup script: invalid command name "image"
while executing
"image create bitmap dnarw -data {
#define down_arrow_width 15
#define down_arrow_height 15
static char down_arrow_bits[] = {
0x00,0x80,0x00,0x80,0x0..."
(file "/usr/local/pgaccess/main.tcl" line 5)
Can anyone Offer any advice on this problem?
pgaccess is a TCL application. You need to be running under X.
--
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 8: explain analyze is your friend
Re: [SQL] Pgaccess problem
On Tuesday 16 December 2003 13:29, vijaykumar M wrote: > Hi All, > I am trying to install pgaccess on a redhat 7.2 linux server with > postgres7.4. Everything seems to be alright but when I go to run pgaccess > I get an error message saying > > Application initialization failed: no display name and no $DISPLAY > environment v > ariable It looks like it's complaining about not running under X. Are you trying to start it from the console? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] failed to build any 5-way joins
Peter Eisentraut wrote: > > The query generating the error is: > > This is useless unless we know table schema, what data is in the tables, > and what software version you use. I can just give you (at the end of this answer) the DDL for the fields I reported in the query, but they are enough because I tried the example I'm giving you (with all tables empty and without FK, while in the original database they had less than 50 rows each) and I got the same result (so, it should be data independent - but I understand that you meant that the error could be data type dependent, so I give you the - reduced - tables definitions). I expected that the error was data independent, because, as I told before, I could not get the plan for the not-working query. Version? In my previous message I told that I'm using 7.4-1 on cygwin distribution. What other info do you need? How can I get them? All I can think more than this is that I use a Windows XP O.S. and that I got this error both using JDBC and using pgAdmin III (but it seems to me that this is an error client-independent). The other problem, however, as I asked before, is: where can I find the documentation for this error (well, besides the source, of course)? are there known limits in joining tables? Thanks again Bye Alessandro Depase CREATE TABLE public.info ( infoid varchar(10) NOT NULL, parent_infoid varchar(10), main_infoid varchar(10), fieldtypeid varchar(10) NOT NULL, CONSTRAINT info_pkey PRIMARY KEY (infoid) ) WITH OIDS; CREATE TABLE public.users_auth_groups ( userid varchar(10) NOT NULL, groupid varchar(10) NOT NULL, CONSTRAINT users_auth_groups_pkey PRIMARY KEY (userid, groupid) ) WITH OIDS; CREATE TABLE public.field_auth ( groupid varchar(10) NOT NULL, fieldtypeid varchar(10) NOT NULL, read_write varchar(1) NOT NULL, CONSTRAINT field_auth_pkey PRIMARY KEY (groupid, fieldtypeid) ) WITH OIDS; CREATE TABLE public.category_auth ( groupid varchar(10) NOT NULL, categoryid varchar(10) NOT NULL, read_write varchar(1), CONSTRAINT category_auth_pkey PRIMARY KEY (groupid, categoryid) ) WITH OIDS; CREATE TABLE public.info_category ( infoid varchar(10) NOT NULL, categoryid varchar(10) NOT NULL, CONSTRAINT info_category_pkey PRIMARY KEY (infoid, categoryid) ) WITH OIDS; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] failed to build any 5-way joins
On Tuesday 16 December 2003 15:34, Alessandro Depase wrote: > People smarter than me are looking at this, but I can give you some quick feedback. > Version? In my previous message I told that I'm using 7.4-1 on cygwin > distribution. What other info do you need? How can I get them? Are you sure it's 7.4.1? I wasn't aware that was publicly released yet. > All I can think more than this is that I use a Windows XP O.S. and that I > got this error both using JDBC and using pgAdmin III (but it seems to me > that this is an error client-independent). It certainly looks like a bug in PG itself, rather than client-related. I'd agree with your guess that it's somewhere in the query rewriter. > The other problem, however, as I asked before, is: where can I find the > documentation for this error (well, besides the source, of course)? are > there known limits in joining tables? I'm not aware of any particular limits on joins. There's even a genetic optimiser that kicks in when joins go above a certain number of tables (default of 12 but configurable in 7.4 iirc?). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] failed to build any 5-way joins
The version declared by the cygwin setup is 7.4-1 (I don't know if the '-1' is the same that your '.1' or, for example, a progressive in the cygwin PostgreSQL build sequence). A 'select version()' answers with this information: PostgreSQL 7.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1 (cygming special) Are there other ways to get a better insight? Alessandro Depase - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Alessandro Depase" <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, December 16, 2003 5:12 PM Subject: Re: [SQL] failed to build any 5-way joins > On Tuesday 16 December 2003 15:34, Alessandro Depase wrote: > > > People smarter than me are looking at this, but I can give you some quick > feedback. > > Version? In my previous message I told that I'm using 7.4-1 on cygwin > > distribution. What other info do you need? How can I get them? > > Are you sure it's 7.4.1? I wasn't aware that was publicly released yet. > > > All I can think more than this is that I use a Windows XP O.S. and that I > > got this error both using JDBC and using pgAdmin III (but it seems to me > > that this is an error client-independent). > > It certainly looks like a bug in PG itself, rather than client-related. I'd > agree with your guess that it's somewhere in the query rewriter. > > > The other problem, however, as I asked before, is: where can I find the > > documentation for this error (well, besides the source, of course)? are > > there known limits in joining tables? > > I'm not aware of any particular limits on joins. There's even a genetic > optimiser that kicks in when joins go above a certain number of tables > (default of 12 but configurable in 7.4 iirc?). > > -- > Richard Huxton > Archonet Ltd > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
