Re: [SQL] Best way to simulate Booleans

[Dirk Jagdmann]

> The most transportable method would be to use either a char(1) or an
> int with a check constraint.
>
> mybool char(1) check (mybool in ('t','f'))
> mybool int check (mybool >=0 and <=1)

I would decide depending on the application requirement. If my Oracle
should look similar to PostgreSQL use the char(1). If you have lots of
application code the int is probably better, since you can just use
the created programming language variable (presumably an integer as
well) in your programming language expressions (if, while).

-- 
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org

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

Re: [SQL] Best way to simulate Booleans

[Simon Riggs]

On Tue, 2009-07-07 at 00:13 -0600, Scott Marlowe wrote:
> On Mon, Jul 6, 2009 at 7:22 PM, Peter Headland wrote:
> > I know, I know, PostgreSQL has Booleans that work very nicely.
> > Unfortunately, I have to create a schema that will work on Oracle as well as
> > PostgreSQL, by which I mean that a single set of Java/JDBC code has to work
> > with both databases. I have an XML meta-schema that enables me to generate
> > appropriate DDL; that handles all the INTEGER vs. NUMBER(m,n) stuff. But
> > Oracle simply has no Booleans, so I will have to resort to some more or less
> > ugly alternative. I am hoping that others here have had to deal with this
> > and can suggest an approach that will be minimally loathsome.
> 
> mybool int check (mybool >=0 and <=1)

Integer works best since it converts easily to boolean

mybool smallint check (mybool in (0, 1))

You can use "char" also, but the syntax is less clear.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

Re: [SQL] Best way to simulate Booleans

[Greg Stark]

On Tue, Jul 7, 2009 at 10:17 AM, Simon Riggs wrote:
>
>
> Integer works best since it converts easily to boolean
>
> mybool smallint check (mybool in (0, 1))
>
> You can use "char" also, but the syntax is less clear.

Hm, I was going to suggest using boolean in postgres and making a
"boolean" domain in Oracle for char(1) and then write all sql to
compare with = 'f' and = 't'. It's annoying you can't use "WHERE
foo_flag" and have to write "WHERE foo_flag = 't'" but otherwise that
would give you the same sql in both flavours.


-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [SQL] fsync and Windows XP

[Jon Norman]

I've tried the different wal_sync_method settings and nothing seems to work.

I don't know if it matters, but I'm running PostgreSQL on an XP Embedded 
system. Initially, I had most of my tables installed in the default 
location, c:\program files\postgresql\8.3\data, on compact flash -- which is 
protected with Enhanced Write Filter (EWF) and uses Hibernate Once Resume 
Many (HORM). My log tables were mapped to a solid state drive using 
tablespace definitions. This configuration didn't seem to work very well so 
I ended up installing everything to the solid state drive that isn't being 
protected with EWF and is not part of the hibernated image. When I 
disconnect power from the system I'd like to see recently added records show 
up on the next boot. If I perform a clean shutdown of the system the records 
written to disk.





What is the trick for getting the fsync feature to work with Windows XP.
I've turned off disk caching and set fsync = on in my postgresql.conf,
but records are still not immediately written to disk.


Check out the wal_sync_method parameter:


Rainer




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

[SQL] FW: Query length limitation in postgres server > 8.2.9

[jacob]

One of our programmers has come to me with a problem. On 3 new Centos 5.3 
servers running Postgres 8.2.13 query's are taking 3500ms-5000ms to complete, 
where the same query on an older server (same hardware, older software 
revisions) the same query on the same data comes back in < 50 ms. After some 
investigation it seems that the new server is refusing to use the index's but 
if I limit the number of arguments in the latter part of the statement to 100 
then it works as expected in the expected amount of time using the indexs. 
Rebuilding the index's in 8.2.13 had no effect.

The Query
EXPLAIN ANALYZE SELECT
    (CASE
    WHEN age < 18 THEN '_18'
    WHEN age >= 18 and age <= 25 THEN 
'18_25'
    WHEN age >= 26 and age <= 35 THEN 
'26_35' 
    WHEN age >= 36 and age <= 50 THEN 
'36_50'
    WHEN age >= 51 and age <= 75 THEN 
'51_75'
    WHEN age > 75 THEN '76_'
    ELSE 'Unspecified'
    END) as ageRange,
    gender,
    sum (current_price_usd * qty_sold) as revenue,
    sum(qty_avail) as available,
    sum(qty_sold) as sold  FROM 
search_site1_2009_03_13
    WHERE buyer_cntry_id = 3
    AND site_id = 1 AND (leaf_category_1 IN
(101658,112734,112735,112736,112737,112738,112739,112740,112741,112742,112743,112744,112745,112746,112747,112748,
112749,112750,3582,72471,72472,90840,93782,91503,27194,101647,72473,72474,11035,11036,27195,72475,11038,11039,
38589,91510,112716,112717,112718,112719,112720,112721,112722,112723,112724,112725,112726,112727,112728,112729,
112730,112731,112732,112698,112699,112700,112701,112702,112703,112704,112705,112706,112707,112708,112709,112710,
112711,112712,112713,112714,112680,112681,112682,112683,112684,112685,112686,112687,112688,112689,112690,112691,
112692,112693,112694,112695,112696,20390,72465,72466,90841,93783,91504,27219,101648,38581,72467,27220,27221,27223,
72468,27224,27225,38582,145519,112752,112768,112753,112754,112755,112756,112757,112758,112759,112760,112761,112762,
112763,112764,112765,112766,112767,11047,11048,38590,68257,27232,90847,93787,101656,91511,27233,101657,38591,38592,
101655,11049,11050,27234,11051,68250,68251,41070,41083,68252,41072,41073,68253,68254,68255,68256,11052,11053,3606,
4305,72483,72484,90842,93784,91505,27236,101649,38594,72486,15112,15113,27237,72485,15115,15116,38614,112806,112807,
112808,112809,112810,112811,112812,112813,112814,112815,112816,112817,112818,112819,112820,112821,112822,49227,112788,
112789,112790,112791,112792,112793,112794,112795,112796,112797,112798,112799,112800,112801,112802,112803,112804,
112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112780,112781,112782,112783,112784,112785,
112786,3641,3719,72489,72490,90843,93785,101650,91506,27252,101651,38616,72491,101652,11055,11056,27253,72492,
11058,11059,1489,11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,11323,11324,27256,72495,11326,
11327,16157,117562,127748,127749,127750,127751,127752,127753,127754,127755,127756,127757,127758,127759,127760,127761,
127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603,68264,27187,27188,27190,27189,
27191,27192,38604,112847,62053) AND Attribute_Value_02 & 2 > 0) GROUP BY 
ageRange, gender;

 
The Result
    
QUERY 
PLAN  
    
   
-

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

[Greg Stark]

On Tue, Jul 7, 2009 at 11:33 PM,  wrote:
> After some investigation it seems that the new server is refusing to use the 
> index's but if I
> limit the number of arguments in the latter part of the statement to 100 then 
> it works as
> expected in the expected amount of time using the indexs.

Ugh, I thought this sounded familiar. I think you're hitting this
limit which was put in place in 8.2.12 to protect against very slow
planning times for very long IN lists:

/*
 * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
 * likely to require O(N^2) time, and more often than not fail anyway.
 * So we set an arbitrary limit on the number of array elements that
 * we will allow to be treated as an AND or OR clause.
 * XXX is it worth exposing this as a GUC knob?
 */
#define MAX_SAOP_ARRAY_SIZE 100


For your situation I'm not sure what to suggest. You could try to make
the query more complex with something like WHERE site_id = 1 AND
(leaf_category in (...) OR leaf_category IN (...)) but I'm not too
hopeful that will work out well.

I wonder if you couldn't get a better plan by stuffing these values
into a temporary table (or even a VALUES query query) and doing a
join. Offhand I don't see any great plan this would result in.

One option would be to recompile postgres with this limit raised. Keep
in mind that the long planning times it was meant to protect against
might start to be a problem, but if you weren't already having a
problem with that in <8.2.11 then perhaps you would be ok.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

[Tom Lane]

Greg Stark  writes:
> On Tue, Jul 7, 2009 at 11:33 PM,  wrote:
>> After some investigation it seems that the new server is refusing to use the 
>> index's but if I
>> limit the number of arguments in the latter part of the statement to 100 
>> then it works as
>> expected in the expected amount of time using the indexs.

> Ugh, I thought this sounded familiar. I think you're hitting this
> limit which was put in place in 8.2.12 to protect against very slow
> planning times for very long IN lists:

Nonsense ... unless perhaps the index is partial, and the IN condition
is relevant to proving it's legal to use the index?  Given the lack of
any schema information in the complaint, I suppose I can't rule that
out, but one would hope the OP would have mentioned such a thing.

regards, tom lane

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