Re: [SQL] Token separation

[Tim Landscheidt]

Tom Lane  wrote:

>> [ "0x13" is lexed as "0" then "x13" ]

>> Is this behaviour really conforming to the standard?

> Well, it's pretty much the universal behavior of flex-based lexers,
> anyway.  A token ends when the next character can no longer sensibly
> be added to it.

I know, but - off the top of my head - in most other lan-
guages "0abc" will then give a syntax error.

> Possibly the documentation should be tweaked to mention the
> number-followed-by-identifier case.

Especially if you consider such cases:

| tim=# SELECT 1D1; SELECT 1E1; SELECT 1F1;
|  d1
| 
|   1
| (1 Zeile)

|  ?column?
| --
|10
| (1 Zeile)

|  f1
| 
|   1
| (1 Zeile)

| tim=#

I don't think it's common to hit this, but the documentation
surely could use a caveat.  I will write something up and
submit it to -docs.

Thanks,
Tim


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

[SQL] UPDATE COMPATIBILITY

[Gera Mel Handumon]

What version of postgresql that the update compatibility below will be
implemented?

UPDATE COMPATIBILITY


UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
 WHERE salesmen.id = accounts.sales_id);


TIA,

-- 
Gera Mel E. Handumon

-
"Share your knowledge. It's a way to achieve immortality" - Dalai Lama

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

[SQL] Wrong query plan when using a left outer join

[Feike Steenbergen]

I have the following setup:

A table called hand:


Table "stage.hand_meta"
Column |   Type   |
Modifiers
---+--+-
 hand_id   | integer  | not null default
nextval('hand_meta_hand_id_seq'::regclass)
 hand_no   | bigint   | not null
 site_id   | smallint | not null
 game_id   | smallint | not null
 time  | timestamp with time zone | not null
 tournament_id | bigint   |
Indexes:
"hand_meta_pkey" PRIMARY KEY, btree (hand_id) CLUSTER
"hand_meta_hand_no_site_unq" UNIQUE, btree (hand_no, site_id)
"hand_meta_time_idx" btree ("time")
"hand_meta_tournament_id_idx" btree (tournament_id)
Referenced by:
TABLE "handhistory_plain" CONSTRAINT
"handhistory_plain_hand_id_fkey" FOREIGN KEY (hand_id) REFERENCES
hand_meta(hand_id)
TABLE "handhistory_staged" CONSTRAINT "staged_hand_hand_id_fkey"
FOREIGN KEY (hand_id) REFERENCES hand_meta(hand_id)

Getting the max hand_id (primary key) results in using an index:


feiketracker=> explain analyze select max(hand_id) from stage.hand;

 QUERY PLAN
---
 Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.379..0.383
rows=1 loops=1)
   InitPlan 1 (returns $0)
 ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual
time=0.337..0.340 rows=1 loops=1)
   ->  Index Scan Backward using hand_meta_pkey on hand_meta
(cost=0.00..82667.12 rows=2479440 width=4) (actual time=0.319..0.319
rows=1 loops=1)
 Index Cond: (hand_id IS NOT NULL)
 Total runtime: 0.823 ms
(6 rows)


Now, if i create a view which left outer joins another table and
select max hand_id it uses a seq_scan, which I think it should'nt use,
as it only needs to query hand_meta and then use the index:


feiketracker=> create view seqscan_example as (select * from hand_meta
left join handhistory_plain using(hand_id));
CREATE VIEW
Time: 72.736 ms

feiketracker=> explain analyze select max(hand_id) from seqscan_example;
 QUERY PLAN
-
 Aggregate  (cost=49261.00..49261.01 rows=1 width=4) (actual
time=34672.052..34672.054 rows=1 loops=1)
   ->  Seq Scan on hand_meta  (cost=0.00..43062.40 rows=2479440
width=4) (actual time=0.180..16725.109 rows=2479440 loops=1)
 Total runtime: 34672.874 ms
(3 rows)


feiketracker=> select version();
  version

 PostgreSQL 9.0.6 on armv5tejl-unknown-linux-gnueabi, compiled by GCC
gcc (GCC) 3.4.4 (release) (CodeSourcery ARM 2005q3-2), 32-bit
(1 row)


I cannot think of a reason to use a seqscan, the left join should
indicate all results from hand_meta should be used, hand_id is the
primary key, so selecting max(hand_id) from the table or the view
should result in the same execution plan or am I thinking wrong?

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