Stephan Szabo (s...@megazone23.bigpanda.com)

User Information

Display Name:

Stephan Szabo

Partial Email Address:

s...@megazone23.bigpanda.com

Posts:

2365	total
235	in PostgreSQL - Admin
201	in PostgreSQL - Bugs
1	in PostgreSQL - Cygwin
1211	in PostgreSQL - General
2	in PostgreSQL - Interfaces
4	in PostgreSQL - JDBC
3	in PostgreSQL - Novice
1	in PostgreSQL - ODBC
81	in PostgreSQL - Performance
1	in PostgreSQL - PHP
690	in PostgreSQL - SQL

5 Most Recent

All Posts

1) Stephan Szabo Re: This works in 7.2.1, not in 7.3.2

+1 vote

It looks like in 7.2 it'd call substring(text, int, int). In 7.3, I don't think that'd be a...

PostgreSQL - General

[ Profile | Reply to group ]

[ Flat Thread Threaded ]

On Thu, 10 Jul 2003, Frank Millman wrote:

> Hi all
>
> The following works in 7.2.1 -
>
> 1. select count(*) from SysUsersCompanies where UserRowId = 3 ;
> 2. select substring('NY',(select count(*) from SysUsersCompanies where UserRowId = 3)+1,1) ;
>
> The first select returns either 1 or 0
> The second one uses the result in a more complex expression, and returns either 'Y' or 'N'
>
> In 7.3.2, the first select behaves the same, but the second one returns NULL.
>
> Any advice will be appreciated.

It looks like in 7.2 it'd call substring(text, int, int).

In 7.3, I don't think that'd be a possible target due to some of the
implicit casting changes. It appears that substring(text, text, text) was
added which is then the valid choice, but it works differently (see the
docs). As a workaround, if you cast the count expression to an integer
(it's a bigint right now) it should do what you want.

2) Stephan Szabo Re: trigger proceedures in sql

+1 vote

AFAICT, no, sql functions cannot return opaque/trigger. However you can write trigger functions in...

PostgreSQL - SQL

[ Profile | Reply to group ]

[ Flat Thread Threaded ]

On Thu, 10 Jul 2003, adivi wrote:

>       can trigger proceedures ( procedures to be executed from within a trigger )
>         not be written in sql.

AFAICT, no, sql functions cannot return opaque/trigger.  However you can
write trigger functions in most/all of the pl languages (plpgsql, pltcl,
etc...)  I think the trigger examples for plpgsql are in plpgsql's
documentation.

3) Stephan Szabo Re: Out of memory

+1 vote

I believe this is fixed in 7.4 (or at least your example does not crash in ...

PostgreSQL - Bugs

[ Profile | Reply to group ]

[ Flat Thread Threaded ]

On Tue, 8 Jul 2003, Ivan Boscaino wrote:

> Hi,
> I'm not sure if you know this problem.
>
> Create an empty function with 26 parameters:
>
> create function test (INTEGER,TEXT,TEXT,TEXT,CHAR(16),CHAR(11),
> BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,
> BOOLEAN,BOOLEAN,BOOLEAN,CHAR(2),TEXT,TEXT,TEXT,TEXT,
> CHAR(5),TEXT,TEXT,TEXT,TEXT,TEXT )
> returns integer as '
> declare
> begin
>   return 0;
> end;' language 'plpgsql';
>
>
> Call it mistyping the boolean parameters:
>
> select test
> (1,'a','a','a','a','a',1,1,1,1,1,1,1,1,1,'a','a','a','a','a','a','a','a','a','a','a');
>
> Then the system crashes.

I believe this is fixed in 7.4 (or at least your example does not crash in
7.4)

4) Stephan Szabo Re: Precision errors in float8 type casting (as of 7.3.2 and

+1 vote

I see no obviously about it. Once you've placed a value in a float you are accepting the chance of...

PostgreSQL - Bugs

[ Profile | Reply to group ]

[ Flat Thread Threaded ]

On Wed, 9 Jul 2003, Philip Edelbrock wrote:

> phil=# select (3.85::float4)::float8;
>       float8
> ------------------
>  3.84999990463257
> (1 row)
>
> (Or substitute 3.85 for any number with something other than 0 to the
> right of the decimal point, or pull the same values from any table which
> stores in float4/real format.)
>
> Obviously, this is wrong and should return 3.85.  We traced this down on

I see no obviously about it.  Once you've placed a value in a float you
are accepting the chance of some precision loss.  When we print a float4
we can print it with an amount of precision that generally limits this
(although you'll see things like 3.849998 -> 3.5), but once you cast it to
a float8 those values are distinguishably different.  In theory one could
keep the history of the value around to determine a precision, but that
doesn't really seem better in general.

5) Stephan Szabo Re: Efficiency of timestamps

+1 vote

Well, the reason I asked is to see both whether the estimates for the various columns were...

PostgreSQL - Performance

[ Profile | Reply to group ]

[ Flat Thread Threaded ]

On Tue, 8 Jul 2003, Martin Foster wrote:

> Stephan Szabo wrote:
>
> > The row estimate is high. How many rows meet the various conditions and
> > some of the combinations? And how many rows does it estimate if you do a
> > simpler query on those with explain?
> >
> > I still think some variety of multi-column index to make the above index
> > conditions would help, but you'd probably need to play with which ones
> > help, and with the cost cut for the limit, I don't know if it'd actually
> > get a better plan, but it may be worth trying a bunch and seeing which
> > ones are useful and then dropping the rest.
> >
> At any given point in time you would not expect to see much more then 30
> posts applying for a time based search.    That is primarily a result of
> having more then one room for which posts are attached to, and then some
> posts exist just to show people are there et cetera.
>
> Simpler queries seem to do quiet well.   That view makes use of the same
> table and seems to have no performance impact from doing as such, and
> the position based search is considerably faster.

Well, the reason I asked is to see both whether the estimates for the
various columns were somewhere near reality (if not, then you may need to
raise the statistics target for the column) which might affect whether
it'd consider using a multi-column index for the conditions and sort
rather than the index scan it was using.

Highlight

Profile | Posts (2365)

Home > People > Stephan Szabo