[SQL] Very strange postgresql behaviour
Hi all, I have postgresql 7.4.2 running on debian and I have the oddest postgresql behaviour I've ever seen. I do the following queries: espsm_asme=# select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 5929 or customer_app_config_id = 11527 order by customer_app_config_id; customer_app_config_id | customer_app_config_name +-- 5929 | INFO (1 row) I do the same query but changing the order of the or conditions: espsm_asme=# select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 11527 or customer_app_config_id = 5929 order by customer_app_config_id; customer_app_config_id | customer_app_config_name +-- 11527 | MOVIDOSERENA TONI 5523 (1 row) As you can see, the configuration 5929 and 11527 both exists, but when I do the queries they don't appear. Here below you have the execution plans. Those queries use an index, I have done reindex table customer_app_config but nothing has changed. espsm_asme=# explain analyze select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 11527 or customer_app_config_id = 5929 order by customer_app_config_id; QUERY PLAN Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.252..0.253 rows=1 loops=1) Sort Key: customer_app_config_id -> Index Scan using pk_cag_customer_application_id, pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27 rows=2 width=28) (actual time=0.168..0.232 rows=1 loops=1) Index Cond: ((customer_app_config_id = 11527::numeric) OR (customer_app_config_id = 5929::numeric)) Total runtime: 0.305 ms (5 rows) espsm_asme=# explain analyze select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 5929 or customer_app_config_id = 11527 order by customer_app_config_id; QUERY PLAN Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.063..0.064 rows=1 loops=1) Sort Key: customer_app_config_id -> Index Scan using pk_cag_customer_application_id, pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27 rows=2 width=28) (actual time=0.034..0.053 rows=1 loops=1) Index Cond: ((customer_app_config_id = 5929::numeric) OR (customer_app_config_id = 11527::numeric)) Total runtime: 0.114 ms (5 rows) The table definition is the following: espsm_asme=# \d customer_app_config Table "public.customer_app_config" Column | Type | Modifiers --+---+ customer_app_config_id | numeric(10,0) | not null customer_app_config_name | character varying(32) | not null keyword | character varying(43) | application_id | numeric(10,0) | not null customer_id | numeric(10,0) | not null customer_app_contents_id | numeric(10,0) | number_access_id | numeric(10,0) | prefix | character varying(10) | separator| numeric(1,0) | default 0 on_hold | numeric(1,0) | not null default 0 with_toss| numeric(1,0) | not null default 0 number_id| numeric(10,0) | param_separator_id | numeric(4,0) | default 1 memory_timeout | integer | with_memory | numeric(1,0) | default 0 session_enabled | numeric(1,0) | default 0 session_timeout | integer | number | character varying(15) | Indexes: "pk_cag_customer_application_id" primary key, btree (customer_app_config_id) "un_cag_kwordnumber" unique, btree (keyword, number_id) "idx_cappconfig_ccontentsid" btree (customer_app_contents_id) "idx_cappconfig_cusidappid" btree (customer_id, application_id) "idx_cappconfig_customerid" btree (customer_id) "idx_cappconfig_onhold" btree (on_hold) "idx_cappconfig_onholdkeyw" btree (on_hold, keyword) Rules: A lot of rules that I don't paste as matter of length. Do you have any idea about how I can fix this? -- Arnau ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if y
Re: [SQL] Very strange postgresql behaviour
On Mon, Jan 29, 2007 at 01:21:13PM +0100, Arnau wrote: > Hi all, > > I have postgresql 7.4.2 running on debian and I have the oddest > postgresql behaviour I've ever seen. I think before you get any help here, you're going to need to upgrade to at least the latest in the 7.4 series. 7.4.2 was a long time ago, and I dimly remember something about data corruption early in the 7.4 series. It could be the source of your problem. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Function returning SETOF using plpythonu
Thanks :-)
That worked fine.
plpy.execute returns dictionary, and you need a list. You may try this:
CREATE FUNCTION "test_python_setof"()
RETURNS SETOF text AS '
records=plpy.execute("SELECT name FROM interface");
return [ (r["name"]) for r in records]
' LANGUAGE 'plpythonu';
Then I tried to do some changes and try to return a SETOF type:
CREATE TYPE "test_python_t" AS (
name varchar(50)
);
CREATE FUNCTION "test_python_setof"()
RETURNS SETOF test_python_t AS '
records=plpy.execute("SELECT name FROM interface");
return [ (r["name"]) for r in records]
' LANGUAGE 'plpythonu';
And I'm getting ERROR: tuple return types are not supported yet.
On my production database server I'm using PostgreSQL 7.4 and using
language plpgsql I'm returning some SETOF type without problems.
Is this a feature that's missing on this version or I'm I doing
something wrong on code? If is a feature missing, is already implemented
on some version afterwards?
Best regards,
Luís Sousa
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Function returning SETOF using plpythonu
On Monday 29 January 2007 6:12 am, Luís Sousa wrote:
> Thanks :-)
> That worked fine.
>
> >plpy.execute returns dictionary, and you need a list. You may try this:
> >
> >CREATE FUNCTION "test_python_setof"()
> >RETURNS SETOF text AS '
> >records=plpy.execute("SELECT name FROM interface");
> >return [ (r["name"]) for r in records]
> >' LANGUAGE 'plpythonu';
>
> Then I tried to do some changes and try to return a SETOF type:
> CREATE TYPE "test_python_t" AS (
> name varchar(50)
> );
>
> CREATE FUNCTION "test_python_setof"()
> RETURNS SETOF test_python_t AS '
> records=plpy.execute("SELECT name FROM interface");
> return [ (r["name"]) for r in records]
> ' LANGUAGE 'plpythonu';
>
> And I'm getting ERROR: tuple return types are not supported yet.
>
> On my production database server I'm using PostgreSQL 7.4 and using
> language plpgsql I'm returning some SETOF type without problems.
> Is this a feature that's missing on this version or I'm I doing
> something wrong on code? If is a feature missing, is already implemented
> on some version afterwards?
>
> Best regards,
> Luís Sousa
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
SETOF in pl/pythonu appeared in 8.2
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Differentiate Between Zero-Length String and NULL Column Values
Hi All, Small problem with 8.2.1, I have a temp table of basic stock details: CREATE TEMP TABLE tmpstk ( code varchar(6), description varchar(38), grp varchar(4), brand text, style text, supplier varchar(6), supplier_code text, wholesale numeric(10,2), retail numeric(10,2), ean varchar(13) ) WITHOUT OIDS; This table is populated using a COPY query works OK. If I try the following query: SELECT * FROM tmpstk I get what I expect, almost 8000 rows including rows similar to the following: "401514","EQUINADE_SHOWSILK_SHAMPOO_5L","3209","EQUINADE","SHAMPOO","EQUEST" ,"401514","0.00","0.00","0.00","0.00","10.00","9329028056594" "401600","A/DRESS_SHAMPOO_TEA_TREE_OIL_500ML","3208","AUSTRALIAN_DRESSAGE"," SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00","" The second row (401600) is what Im interested in for this particular problem. The problem is when I try and add a WHERE clause: SELECT * FROM tmpstk WHERE ean = ''; SELECT * FROM tmpstk WHERE TRIM(ean) = ''; SELECT * FROM tmpstk WHERE ean = NULL; None of the above queries return any rows. What am I doing wrong? It should return the 401600 row above, along with several hundred other rows. I have attached the bash script that actually does this processing. Help is much appreciated. Cheers, -p Phillip Smith IT Coordinator Weatherbeeta P/L AUSTRALIA E. [EMAIL PROTECTED] ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments import-wb-stock.sh Description: Bourne shell script ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Differentiate Between Zero-Length String and NULL Column Values
On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote: > The second row (401600) is what I'm interested in for this particular > problem. The problem is when I try and add a WHERE clause: > SELECT * FROM tmpstk WHERE ean = ''; > SELECT * FROM tmpstk WHERE TRIM(ean) = ''; > SELECT * FROM tmpstk WHERE ean = NULL; > None of the above queries return any rows. Checking for equality against NULL won't work unless you have transform_null_equals set, which you shouldn't. Use IS NULL instead: SELECT * FROM tmpstk WHERE ean IS NULL; -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
