[BUGS] like not using indexes in 7.3b5?
I have a table with a bunch of varchar columns. In 7.2.3 Postgres would indexes on varchar column for certain like queries. This is a script from 7.2.3: lars=> \d agency Table "agency" Column | Type | Modifiers -+---+--- nr | integer | name1 | character varying(20) | name2 | character varying(20) | street | character varying(20) | city| character varying(10) | zip | character varying(10) | phone | character varying(15) | blocked | boolean | Indexes: agency_zip lars=> \d agency_zip Index "agency_zip" Column | Type +--- zip| character varying(10) btree lars=> explain select * from agency where zip = 'abc'; NOTICE: QUERY PLAN: Index Scan using agency_zip on agency (cost=0.00..3.11 rows=8 width=90) EXPLAIN lars=> explain select * from agency where zip like 'abc'; NOTICE: QUERY PLAN: Index Scan using agency_zip on agency (cost=0.00..3.11 rows=8 width=90) EXPLAIN lars=> explain select * from agency where zip like 'abc%'; NOTICE: QUERY PLAN: Index Scan using agency_zip on agency (cost=0.00..3.01 rows=1 width=90) EXPLAIN The same table 7.3b5 results in this: - lars=> explain select * from agency where zip = 'abc'; QUERY PLAN --- Index Scan using agency_zip on agency (cost=0.00..393.64 rows=112 width=124) Index Cond: (zip = 'abc'::character varying) (2 rows) lars=> explain select * from agency where zip like 'abc'; QUERY PLAN Seq Scan on agency (cost=0.00..653.50 rows=112 width=124) Filter: (zip ~~ 'abc'::text) (2 rows) lars=> explain select * from agency where zip like 'abc%'; QUERY PLAN Seq Scan on agency (cost=0.00..653.50 rows=112 width=124) Filter: (zip ~~ 'abc%'::text) (2 rows) i.e. is correctly uses the index for the = operator but not for like (if used as prefix operator) In both cases the tables are "analyzed" and the like queries execute slower in 7.3b5. Is that expected behavior in 7.3? -- Lars ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Bug #811: Using || with char and char varying
John Lim ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Using || with char and char varying Long Description The following sql: select firstname||lastname from adoxyz; generates this error: PostgreSQL said: ERROR: Unable to identify an operator '||' for types 'character' and 'character varying' You will have to retype this query using an explicit cast Based on the table: CREATE TABLE "adoxyz" ( "id" int4, "firstname" char(24), "lastname" varchar, "created" date ); Although this can be fixed by a typecast, porting sql (which i am doing) from other databases such as oracle/mysql is a big pain. Thanks for looking into this. Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Problem with a sequence being acted on by an on insert rule.
Apologies if this bug has already been reported and I am also reasonably
new to postgresql so I might be doing something stupid :-)
Anyway to replicate the problem:
Initial tables:
- create sequence autonumber increment 1 minvalue 0 start 0;
- create table testtable1 ( pk int primary key );
- create table testtable2 ( fk int primary key references
testtable1(pk));
- create rule updatetesttable2 as on insert to testtable1 do (insert
into testable2 (fk) values( new.pk ));
Testing:
- insert into testtable1 values ('1');
- select * from testtable2;
fk
1
(1 row)
The Problem:
- insert into testtable1 values ( (select nextval('autonumber')));
ERROR: referential integrity violation - key referenced from
testtable2 not found in testtable1
This confused me for a while until I did some testing and removed the
foreign key from testtable2 i.e.
- drop table testtable2;
- drop rule updatetesttable2;
- create table testtable2 (fk int);
- create rule updatetesttable2 as on insert to testtable1 do (insert
into testable2 (fk) values( new.pk ));
Now we have:
- insert into testtable1 values ( ( select nextval('autonumber')));
- select * from testtable1;
pk
-
1
155
(2 rows)
- select * from testtable2;
fk
-
156
(1 row)
So in other words the rule action new.pk actually pulled the next number
from the sequence autonumber thus failing the refential integrity checks
above. There is no rush to fix this as I have modified the rule as
follows which works but still wastes a sequence number:
- create rule updatetesttable2 as on insert to testtable1 do (insert
into testable2 (fk) values( new.pk - 1 ));
Version information as follows:
- Linux distro - Debian stable
- Kernel version 2.4.19
- select version();
version
---
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
Thanks
Mark
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] Sequence Start number not dumped correctly
Hi, When I have a sequence like this: CREATE SEQUENCE "test_id_seq" start -32768 increment 1 maxvalue 32767 minvalue -32768 cache 1; and dump it using pg_dump -s -c -f schema.txt testdb it will show up in schema.txt like this: CREATE SEQUENCE "test_id_seq" start 1 increment 1 maxvalue 32767 minvalue -32768 cache 1; This makes me think that the start value is hardwired somewhere, and I wanted to let you know. PostgreSQL is great! I use it for everything! Thanks a lot --reto ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] like not using indexes in 7.3b5?
Lars <[EMAIL PROTECTED]> writes: > Is that expected behavior in 7.3? It is if you initdb'd in a locale other than "C". Use pg_controldata to check. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Problem with a sequence being acted on by an on insert
On 5 Nov 2002, Mark Le Huray wrote:
> Apologies if this bug has already been reported and I am also reasonably
> new to postgresql so I might be doing something stupid :-)
>
> Anyway to replicate the problem:
>
> Initial tables:
>
> - create sequence autonumber increment 1 minvalue 0 start 0;
> - create table testtable1 ( pk int primary key );
> - create table testtable2 ( fk int primary key references
> testtable1(pk));
> - create rule updatetesttable2 as on insert to testtable1 do (insert
> into testable2 (fk) values( new.pk ));
You probably really want a trigger, not a rule for this. Rules are like a
substitution system, so the second insert becomes something like insert
into testtable2(fk) values ((select nextval('autonumber')); This is
the expected behavior.
I think both currval('autonumber') and your solution (-1) will fail to
do what you expect if multiple rows are being inserted in one insert
statement (insert .. select for example).
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Sequence Start number not dumped correctly
Reto Stamm <[EMAIL PROTECTED]> writes: > When I have a sequence like this: > CREATE SEQUENCE "test_id_seq" start -32768 increment 1 maxvalue 32767 minvalue > -32768 cache 1; > it will show up in schema.txt like this: > CREATE SEQUENCE "test_id_seq" start 1 increment 1 maxvalue 32767 minvalue -32768 > cache 1; How old is your Postgres? It works here. But note that there is probably also a select setval() for the sequence, which is what *really* determines the starting value. 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] Problem with a sequence being acted on by an on insert rule.
Mark Le Huray <[EMAIL PROTECTED]> writes:
> - create sequence autonumber increment 1 minvalue 0 start 0;
> - create table testtable1 ( pk int primary key );
> - create table testtable2 ( fk int primary key references
> testtable1(pk));
> - create rule updatetesttable2 as on insert to testtable1 do (insert
> into testable2 (fk) values( new.pk ));
> - insert into testtable1 values ( (select nextval('autonumber')));
This does not work very well, as you've discovered, because a rule is
really a macro. As such, it has all the usual issues with multiple
evaluations of arguments.
I'd recommend a trigger, not a rule, for propagating information from
one table to another. Although the notational cruft is messier, the
trigger will likely be faster as well as impervious to this type of
problem.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
Bug: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Affects: PL/pgSQL Severity: Annoyance Priority: Minor Enhancement Confirmed On: 7.3beta2, Linux Given the following function: === create or replace function rowtype_test () returns text as ' declare this_row candidates%rowtype; that_row candidates%rowtype; begin select * into this_row from candidates; that_row := this_row; return that_row.first_name; end;' language 'plpgsql'; === ... it will error out at the assignment "that_row := this_row". For that matter, any attempt to assign the contents of two ROWTYPE or RECORD variables directly to each other will error out: that_record := this_record; SELECT this_row INTO that_row; SELECT * INTO that_row FROM this_row; The only way to populate that_row with a copy of this_row is by re-querying the source table. While a relatively easy workaround, this behaviour is annoying and inconsistent. It would be nice to fix in 7.3.1 or 7.4. Thanks for your attention. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] pg_ctl is fragile
After an unclean shutdown (e.g. a kernel crash), pg_ctl isn't smart enough to realize that it won't be able to successfully shut down the postmaster: $ pg_ctl -D /pgsql/data stop /pgsql/bin/pg_ctl: line 269: kill: (2039) - No such process waiting for postmaster to shut down failed pg_ctl: postmaster does not shut down We should probably check the exit code produced by kill(1). Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pg_ctl is fragile
Neil Conway <[EMAIL PROTECTED]> writes: > After an unclean shutdown (e.g. a kernel crash), pg_ctl isn't smart > enough to realize that it won't be able to successfully shut down the > postmaster: > $ pg_ctl -D /pgsql/data stop > /pgsql/bin/pg_ctl: line 269: kill: (2039) - No such process > waiting for postmaster to shut >down failed > pg_ctl: postmaster does not shut down > We should probably check the exit code produced by kill(1). Doesn't seem like kill's exit code is going to tell you enough. If you got, say, "Permission denied" rather than "No such process", you shouldn't report that the postmaster isn't running. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pg_ctl is fragile
Tom Lane <[EMAIL PROTECTED]> writes: > Doesn't seem like kill's exit code is going to tell you enough. > If you got, say, "Permission denied" rather than "No such process", > you shouldn't report that the postmaster isn't running. Ok, fair enough -- so is there any way to improve this behavior? Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(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] Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
Deep copy with User defined data types sometimes get a little wild, possibly with alignment and memory context. For example a UDT which is a char followed by an int might be tricky to recognize that alignment might be needed. It might even be better to have the UDT writer write their own deep copy function if their type is not compatible with a straight memcpy. One of the other reasons this was a real PITB at informix was that columns could also contain row (composite) types. We do not have that feature (yet?), but if deep copy is done in a type blind way which is open to adding recursion we would not shut the door on the possibility. Tables have rows which have columns containing rows which have columns containing udts and rows However, I suspect that postgresql row handling is a lot cleaner than the informix row handling (with or without rows as columns) and it has been a while since I looked at the problem so maybe it is a non-issue. But I'm raising it just in case... elein On Thursday 07 November 2002 13:56, Neil Conway wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > create or replace function rowtype_test () > > returns text as ' > > declare this_row candidates%rowtype; > > that_row candidates%rowtype; > > begin > > select * into this_row > > from candidates; > > > > that_row := this_row; > > > > return that_row.first_name; > > > > end;' > > language 'plpgsql'; > > === > > > > ... it will error out at the assignment "that_row := this_row". > > So we'd want a deep copy, right? > > > The only way to populate that_row with a copy of this_row is by > > re-querying the source table. > > Well, you can also iterate through the fields of this_row and assign > them to that_row manually -- of course, that's not much better. > > > While a relatively easy workaround, this behaviour is annoying and > > inconsistent. It would be nice to fix in 7.3.1 or 7.4. > > Unless anyone sees a problem with this, I'll work on this. I > definately think it's inappropriate for 7.3.1 though. > > Cheers, > > Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
Josh Berkus <[EMAIL PROTECTED]> writes: > create or replace function rowtype_test () > returns text as ' > declare this_row candidates%rowtype; > that_row candidates%rowtype; > begin > select * into this_row > from candidates; > > that_row := this_row; > > return that_row.first_name; > > end;' > language 'plpgsql'; > === > > ... it will error out at the assignment "that_row := this_row". So we'd want a deep copy, right? > The only way to populate that_row with a copy of this_row is by re-querying > the source table. Well, you can also iterate through the fields of this_row and assign them to that_row manually -- of course, that's not much better. > While a relatively easy workaround, this behaviour is annoying and > inconsistent. It would be nice to fix in 7.3.1 or 7.4. Unless anyone sees a problem with this, I'll work on this. I definately think it's inappropriate for 7.3.1 though. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] pg_ctl is fragile
Neil Conway wrote: > Tom Lane <[EMAIL PROTECTED]> writes: > > Doesn't seem like kill's exit code is going to tell you enough. > > If you got, say, "Permission denied" rather than "No such process", > > you shouldn't report that the postmaster isn't running. > > Ok, fair enough -- so is there any way to improve this behavior? Seems we could grab some of kill's stderr and grep it for "No such": $ kill -0 32323 bash: kill: (32323) - No such pid Probably can't hurt. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
Neil, > Unless anyone sees a problem with this, I'll work on this. I > definately think it's inappropriate for 7.3.1 though. Thank you! -Josh Berkus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
