[BUGS] ecpg did not precompile declare cursor
This is expected behaviour, the 'real' code gets emitted when you OPEN
the cursor, i.e. you should be doing something like:
EXEC SQL DECLARE demo_cur CURSOR FOR
SELECT field1, field2
FROM test;
EXEC SQL OPEN demo_cur;
if( sqlca.sqlcode != 0 )
{
some_error();
return;
}
while( 1 )
{
EXEC SQL FETCH demo_cur INTO :field1, :field2;
if( sqlca.sqlcode < 0 )
{
some_error();
break;
}
else if( sqlca.sqlcode != 0 ) /* or == 100... */
break;
process_row();
}
EXEC SQL CLOSE demo_cur;
Bernhard Rückerl writes:
> Hello,
>
> I have downloaded postgresql 7.2 on my machine.
> Running ecpg on my .ec-file I found that ecpg did not process
> the statements "exec sql declare xxx cursor for select.
>
> The part in my .ec-file:
> if ( firstcall )
>{
>calid1 = calid;
>EXEC SQL DECLARE CURMFDPOINT CURSOR FOR SELECT STABLE_OR_INSTABLE ,
> HIERARCHY , POINT_ID , X1 , P1 , X2 , P2 FROM MANIFOLD_POINTS WHERE
> CAL_ID = :calid1;
>raiseerror( );
>firstcall = false;
>}
>
> was transformed into
> if ( firstcall )
>{
>calid1 = calid;
>/* declare CURMFDPOINT cursor for select STABLE_OR_INSTABLE , HIERARCHY ,
>POINT_ID , X1 , P1 , X2 , P2 from MANIFOLD_POINTS where CAL_ID = ? */
> #line 224 "dbcontrol.ec"
>
>raiseerror( );
>firstcall = false;
>}
> So the declare cursor statement was just commented out. As a consequence the
> programm terminated with sqlca.sqlcode=-602 when doing the according
> fetch statement.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Bug #598: optimizer: convert 'IN' to join
piers haken ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description optimizer: convert 'IN' to join Long Description the optimizer should do better than a sequential scan with statements like: SELECT * FROM t1 WHERE t1.index IN (SELECT t2.index FROM t2); this gives: Seq Scan on t1 SubPlan -> Seq scan on t2 this is equivalent to (and should be transformed to) SELECT t1.* FROM t1, t2 WHERE t1.index = t2.index; which gives the much faster: Nested Loop -> Seq Scan on t1 -> Index Scan using t2_pkey on t2 FYI: SQL Server generates a hash table from t1 and probes it with pkey values of t2 read from a NON-primary index. For SQL Server, scanning a non-primary key takes fewer disk reads than scanning a primary key. if you add a condition to the subquery on a non-unique column: SELECT * FROM t1 WHERE t1.index IN (SELECT t2.index FROM t2 WHERE t2.value='something'); or SELECT t1.* FROM t1, t2 WHERE t1.index=t2.index AND t2.value='something'; you also get two different plans: Seq Scan on t1 SubPlan -> Materialize -> Index Scan using ix_t2_value on t2 as opposed to Hash Join -> Seq Scan on t1 -> Hash -> Index Scan using ix_t2_value on t2 Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Dates and year 2000
Is this a bug? "Andy Marden" <[EMAIL PROTECTED]> wrote in message news:a4u6fh$orp$[EMAIL PROTECTED]... > Am loading date fields from text in one table to date in another. Format of > the text dates is 'DD.MM.YY', so that's the format mask I use. Dates for > 2001 work OK - '02.09.01' translates as '2001-09-02', but '02.09.00' > translates to '0001-09-02 BC'! The y2k.html part of the integrated doc says > that 70 - 69 equates 1970 - 2069. > > Am I missing something? > > Version 7.1.3 on RH 7.2 BTW > > Cheers > > Andy > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Dates and year 2000
"Andy Marden" <[EMAIL PROTECTED]> writes: > Is this a bug? Yes. It's fixed in 7.2 ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [GENERAL] schema error upgrading from 7.1 to 7.2
Vivek Khera <[EMAIL PROTECTED]> writes: > "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL, The above was never correct. I believe that 7.1's rather lax date parser might have interpreted the literal as being 'current'. 7.2's date parser would reject it, even if 'current' were still an allowed value, which it is not. On the other hand, invoking the function CURRENT_DATE "owner_lastbilled" date DEFAULT CURRENT_DATE NOT NULL, was and remains valid. > This is not documented in the list of changes to the Schema > Manipulation. Because it is not one: it is a datatype behavioral change. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] [GENERAL] schema error upgrading from 7.1 to 7.2
Vivek Khera <[EMAIL PROTECTED]> writes: > Then how come pg_dump outputs it that way? Is it because that's how I > did it when creating the schema in the first place? Presumably. > TL> Because it is not one: it is a datatype behavioral change. > It isn't documented in the HISTORY file in any way shape or form. HISTORY says, under "Migration to version 7.2": * The date/time value 'current' is no longer available. You will need to rewrite your applications. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] schema error upgrading from 7.1 to 7.2
During the upgrade from 7.1.3 to 7.2, I encountered an incompatibility
with the schema defs.
Prior to the upgrade, I used the 7.1.3 pg_dump program to create a
compressed dump:
pg_dump -Fc vk > vk.dump
then, using the 7.2 pg_restore, I exctracted the table schema
definitions:
pg_restore -l vk.dump >vk.1
edit vk.1 to just extract TABLE defs and ACLs (everything prior to
DATA parts)
pg_restore -L vk.1 vk.dump > vk.schema
psql vk < vk.schema
results in the complaint about 'CURRENT_DATE' as shown in the boiled
down example below. The line it complains about from the schema is
"owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL,
The fix seems to be to remove the single quotes around CURRENT_DATE.
pg_restore should be taught this, I think.
pg_dumpall from 7.1.3 creates the same (now invalid) schema too.
This is not documented in the list of changes to the Schema
Manipulation.
A boiled down example:
khera=> create table foo ("owner_id" integer not null, "owner_lastbilled" date DEFAULT
'CURRENT_DATE' NOT NULL);
ERROR: Bad date external representation 'CURRENT_DATE'
khera=> create table foo ("owner_id" integer not null, "owner_lastbilled" date DEFAULT
CURRENT_DATE NOT NULL);
CREATE
khera=> \d foo
Table "foo"
Column | Type | Modifiers
--+-+
owner_id | integer | not null
owner_lastbilled | date| not null default date('now'::text)
khera=> insert into foo (owner_id) values (1);
INSERT 16966 1
khera=> select * from foo;
owner_id | owner_lastbilled
--+--
1 | 2002-02-20
(1 row)
khera=> select version();
version
---
PostgreSQL 7.2 on i386-unknown-freebsd4.4, compiled by GCC 2.95.3
(1 row)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] [GENERAL] schema error upgrading from 7.1 to 7.2
> "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> Vivek Khera <[EMAIL PROTECTED]> writes: >> "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL, TL> The above was never correct. I believe that 7.1's rather lax date TL> parser might have interpreted the literal as being 'current'. 7.2's Then how come pg_dump outputs it that way? Is it because that's how I did it when creating the schema in the first place? I guess I extended putting the quotes around that because of the warnings about putting quotes around 'NOW()' as a default. My mistake... TL> Because it is not one: it is a datatype behavioral change. It isn't documented in the HISTORY file in any way shape or form. Where else should I look for potential traps in validating my app under 7.2? Thanks. ---(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
[BUGS] Trying Cygwin version of PostgreSQL
Hello PostgreSQL, I am trying to install the PostgreSQL 7.2 downloaded by Cygwin but the install does not match any of the documentation. Help! Last year I installed PostgreSQL 7.1 on NT for a one off project then deleted it. I used the pg download then added Cygwin using the Cygwin download. Everything worked after I put together documentation from several included files, a few web pages and lots of experiments. This time I am attempting to use the Cygwin download including PostgreSQL. The documentation is scattered over several documents and none match the actual installation process. I resorted to trial and error. Initdb hangs when "Creating template1 database" so there must be a step missing. Given the many "try this" paragraphs across several documents, I estimate the number of combinations will exceed the storage capacity of a 32 bit integer. Should I give up on Cygwin and go back to what I did last time? Is there a document or web page that describes a successful installation step by step? Peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Dates and year 2000
> Is this a bug? > > Am loading date fields from text in one table to date in another. > > Format of the text dates is 'DD.MM.YY', so that's the format mask > > I use. Dates for 2001 work OK - '02.09.01' translates as > > '2001-09-02', but '02.09.00' translates to '0001-09-02 BC'! > > The y2k.html part of the integrated doc says that 70 - 69 equates > > 1970 - 2069. > > Am I missing something? > > Version 7.1.3 on RH 7.2 BTW What do you mean by "format mask I use"? Are you trying to enter things like insert into t1 values (date '02.09.01'); insert into t1 values (date '02.09.00'); ... or are you using to_date() which has the notion of templates or "masks"? lockhart=# select date '02.09.01'; 2001-02-09 lockhart=# select date '02.09.00'; 2000-02-09 lockhart=# select version(); - PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96 thomas=# select date '02.09.00'; date 2000-02-09 thomas=# select version(); --- PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96 So, no bug unless you can be more explicit about your test case... - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Trying Cygwin version of PostgreSQL
Peter <[EMAIL PROTECTED]> writes: > I resorted to trial and error. Initdb hangs when "Creating template1 > database" so there must be a step missing. I think you forgot to run the cygipc daemon. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Dates and year 2000
Do we have a workaround for 7.1.3? I don't really want to risk an upgrade at this stage in the system Cheers Andy - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Andy Marden" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, February 20, 2002 11:21 PM Subject: Re: [BUGS] Dates and year 2000 > "Andy Marden" <[EMAIL PROTECTED]> writes: > > Is this a bug? > > Yes. It's fixed in 7.2 ... > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
