[SQL] FIFO Queue Problems

[Chris Gamache]

I'm having a race condition with a FIFO queue program that I've created...

CREATE TABLE fifo (
  id serial,
  data varchar(100),
  status int4 DEFAULT 0
);

I have a program that claims a row for itself

  my $processid = $$;
  my $sql_update = 

[SQL] Using VIEW to simplify code...

[Chris Gamache]

Right now I dynamicly generate the SQL for an incredibly ugly 4 table join
based on user information client-side. I got the bright idea to create a view
and then run a MUUUCH simpler client-side query on that view. The problem is
that PostgreSQL apparantly runs the view FIRST and then applies the criteria
AFTER assembling the whole view.

I was hoping that it would rewrite the "select" in the view to include the
criteria BEFORE running the view. That way it could take advantage of the
indexes the way my giant-and-hard-to-maintain-client-generated-sql does.

Any thoughts or suggestions?

If you MUST have the giant-and-hard-to-maintain-client-generated-sql statement
and its related explain, I'll produce it. I cringe at the thought of having to
redact that monster, tho.

CG

__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Using VIEW to simplify code...

[Chris Gamache]

> If you want help, you must provide details.  The PG version number is
> relevant also.

Understood. PostgreSQL 7.2.3.

Here's the generated client side sql:

select 
   case when (hasflag(ut.flags,1) or (current_timestamp - '1 day'::interval <
trl.trans_date)) then 
 case when trl.trans_data like '%RNF'then 
   ' ' 
   else 
 'Free' 
   end 
 else 
   case when trl.trans_data like '%RNF' then  
 ' ' 
   else 
 case when ct.misc_charge = '0'::money then  
   'Free' 
 else 
   'View for ' || to_char(ct.misc_charge::float8,'FM$9990D90') || '' 
 end 
   end 
 end as " ", 
trl.trans_date::timestamp(0) as "Date",  
tl.longtype as "Type",  
trl.trans_data as "Query Data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged",  
trl.user_reference_id as "Reference ID"  
from trans_log_1 trl, addtypelong tl, user_table ut, company_table ct 
where  
((trl.username='myuser') and  
(trl.username=ut.username) and 
(ut.company_name=ct.company_name) and 
(trl.trans_date >= current_timestamp-'60 days'::interval) and  
(tl.shorttype=trl.trans_type) )  
union all  
select  
 case when (hasflag(ut.flags,16) or (current_timestamp - '1 day'::interval <
trl.trans_date)) then  
 case when trl.trans_data like '%RNF' then  
 ' ' 
 else 
 'Free' 
 end 
 else 
 case when trl.trans_data like '%RNF' then  
 ' ' 
 else 
 case when ct.misc_charge = '0'::money then  
 'Free' 
 else 
 'View for ' || 
to_char(ct.misc_charge::float8,'FM$9990D90') || '' 
 end 
 end 
 end as " ", 
trl.trans_date::timestamp(0) as "Date",  
tl.longtype as "Type",  
trl.trans_data as "Query Data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged",  
trl.user_reference_id as "Reference ID"  
from trans_log_2 trl, addtypelong tl, user_table ut, company_table ct 
where 
((trl.username='myuser') and  
 (trl.username=ut.username) and 
 (ut.company_name=ct.company_name) and 
 (trl.trans_date >= current_timestamp-'60 days'::interval) and  
 (tl.shorttype=trl.trans_type) )  
union all  
select 
 case when trans_type = 'NS' then  
 ' ' 
 else 
 case when (hasflag(ut.flags,16) or (current_timestamp - '1 
day'::interval <
trl.trans_date)) then  
 case when trl.trans_data like '%RNF' then  
 ' ' 
 else 
 'Free' 
 end 
 else 
 case when trl.trans_data like '%RNF' then  
 ' ' 
 else 
 case when ct.misc_charge = '0'::money then  
 'Free' 
 else ' ' 
 end 
 end 
 end 
 end as " ",  
trl.trans_date::timestamp(0) as "Date",  
case when trl.trans_type = 'NS' then 'Name' else 'Archive: ' || tl.longtype end
as "Type",  
trl.trans_data as "Query Data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged",  
trl.user_reference_id as "Reference ID"  
from trans_log_3 trl, addquerytype tl, user_table ut, company_table ct 
where 
((trl.username='myuser') and  
 (trl.username=ut.username) and 
 (ut.company_name=ct.company_name) and 
 (trl.trans_date >= current_timestamp-'60 days'::interval) and 
 (tl.querytype=trl.trans_type) )  
union all 
select 
 case when (fdf is null or fdf='') then  
 ' ' 
 else 
 'Free' 
 end as " ",  
trl.trans_date::timestamp(0) as "Date", 
'FORM: ' || trl.trans_type as "Type", 
trl.trans_data as "Query Data", 
to_char(trl.trans_charge, 'FM$9990D90') as "Charged", 
user_reference_id as "Reference ID" 
from trans_log_4 trl, user_table ut, company_table ct 
where 
((trl.username='myuser') and  
 (trl.username=ut.username) and 
 (ut.company_name=ct.company_name) and 
 (trl.trans_date >= current_timestamp-'60 days'::interval) ) 
order by 2 desc, 4 LIMIT 20 OFFSET 0;

Explain:

Limit  (cost=4339.83..4339.83 rows=20 width=158)
  ->  Sort  (cost=4339.83..4339.83 rows=285 width=158)
->  Append  (cost=2477.60..4328.19 rows=285 width=158)
  ->  Subquery Scan *SELECT* 1  (cost=2477.60..2578.56 rows=187
width=157)
->  Hash Join  (cost=2477.60..2578.56 rows=187 width=157)
  ->  Seq Scan on company_table ct  (cost=0.00..80.41
rows=1041 width=32)
  ->  Hash  (cost=2477.13..2477.13 rows=187 width=125)
->  Hash Join  (cost=287.56..2477.13 rows=187
width=125

Re: [SQL] Using VIEW to simplify code...

[Chris Gamache]

--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Tue, 19 Nov 2002, Chris Gamache wrote:
> >> Understood. PostgreSQL 7.2.3.
> 
> > 7.3 will be better for this. There were questions about the safety
> > of pushing clauses down in queries with union and intersect and
> > earlier versions wouldn't push down because we weren't sure it was
> > safe.  Except will still be problematic, but union and intersect should be
> > much happier.
> 
> Yeah, the UNIONs in the view are definitely the big problem.  Can you
> try on 7.3rc1 and see how it goes?

That's something to look forward to! I'm going to have to hold off upgrading
until y'all feel like 7.3rc1 should become 7.3.0. I wish we had a development
environment to use, but we don't.  I'll let you know how things fare after
that.

CG

__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com

---(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

[SQL] A Costly function + LIMIT

[Chris Gamache]

PostgreSQL 7.2.3

I have a function that is quite costly to run on 1000's of records... Let's
call it "widget". 

I have a query

(SELECT name, address, city, state, zip, widget(name, address, city, state,
zip) 
FROM eastern_usa 
ORDER BY state, city, zip, name 
LIMIT 5000)

UNION ALL

(SELECT name, address, city, state, zip, widget(name, address, city, state,
zip) 
FROM western_usa 
ORDER BY state, city, zip, name 
LIMIT 5000)

ORDER BY 4, 3, 5, 1 LIMIT 100 OFFSET 0;

It runs "widget" on 1 records. The damage would be negligible if it could
run on the 100... 

Any ideas?

CG

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---(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

[SQL] Race condition w/ FIFO Queue reappears!

[Chris Gamache]

Tom's suggested solution worked GREAT in 7.2.x ... I tried taking the plunge
into 7.3.1 tonight. In 7.3.1, when my FIFO queue program goes to grab a row,
TWO processes grab the same row, almost without fail. I even changed my locking
statement to the dreaded 

LOCK TABLE fifo IN ACCESS EXCLUSIVE MODE;

it still exhibits the same behavior. I've tried variations on the theme, but I
can't seem to figure it out. I'm stumped!

The postgresql configuration is as identical (IMO) as I could possibly make it
considering the changes from 7.2 to 7.3. I can't imagine a config option would
control something so basic. I can't find any reference to it in the 7.3 docs,
and my tired eyes did not pick any fixes remotely pertaining to this type of
locking problem in the HISTORY file.

I'm (sadly) switching back to 7.2 until we can figure this out.

CG

>Chris Gamache <[EMAIL PROTECTED]> writes:
>> I have a program that claims a row for itself 
>>   my $processid = $$;
>>   my $sql_update = <> UPDATE fifo
>>   set status=$processid
>> WHERE id = (SELECT min(id) FROM fifo WHERE status=0);
>> EOS 
>> The problem occurrs when two of the processes grab the exact same row at the
>> exact same instant.
>
>Probably the best fix is to do it this way:
>
>   BEGIN;
>   LOCK TABLE fifo IN EXCLUSIVE MODE;
>   UPDATE ... as above ...
>   COMMIT;
>
>The exclusive lock will ensure that only one process claims a row
>at a time (while not preventing concurrent SELECTs from the table).
>This way you don't need to worry about retrying.
>
>   regards, tom lane


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Splitting text into rows with SQL

[Chris Gamache]

Using Postgresql 7.2.3 ...

In order to search using indexes I need to split a composite field into its
components and return it as rows... If this would only work:



create table table_with_composite_fields (
data1 serial,
data2 varchar(100),
composite_field text
);

insert into table_with_composite_fields (data2, composite_field) values
('something1','---,---,---');


create table other_table (
data3 serial,
data4 varchar(100),
uuid uniqueidentifier
);

create index 'other_table_uuid_idx' on other_table(uuid);

insert into other_table (data4, uuid) values
('something2','---');

insert into other_table (data4, uuid) values
('something3','---');

insert into other_table (data4, uuid) values
('something4','---');

select * from other_table ot where ot.uuid in (select split(composite_field)
from table_with_composite_field where data1=1) order by data3;

 data3 |   data4|   uuid
---++-
 1 | something2 | ---
 2 | something3 | ---
 3 | something4 | ---



any ideas for creating my fictional "split" function? I don't mind if the
solution is head-slapping-ly simple. I'm too close to the problem and can't
seem to figure it out!

CG

__
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Elegant SQL solution:

[Chris Gamache]

There are so many (bad) ways to skin this cat... I'm looking for a more elegant
solution.

If I

SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
BY month;

It might only return

 month | rows
---+--
 1 | 234
 3 | 998
 4 | 403
 5 | 252
 10| 643
 12| 933

I would like:

 month | rows
---+--
 1 | 234
 2 | 0
 3 | 998
 4 | 403
 5 | 252
 6 | 0
 7 | 0
 8 | 0
 9 | 0
 10| 643
 11| 0
 12| 933


I could create a one-column table with values 1 - 12 in it, and select from
that table with a where clause matching "month". I could also create a view 
"SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
a more elegant way to do this.

Any thoughts?


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] undefine currval()

[Chris Gamache]

I'm using sequences and currval() to retrieve the last inserted row in a table.


If currval() is undefined, as it is when a connection is made, then I know no
rows were inserted in that table and can take a different action. This is
problematic when using a connection pooling library, as the value of currval()
for any given sequence could possibly be set from a previous "connection".

One (theoretical) workaround would be to issue some sort of command to the
back-end database to wipe all values of currval() when a "new" connection is
made. I've done some digging in the system tables and source code, and can't
find an obvious solution. Perhaps one you you gurus can suggest a SQL statement
to do such a thing. 

Alternately, if there is a better way to retrieve the last inserted row for any
given table, I'd be very grateful for the tip. It would need to be independent
of the connection history, and undefined if there has not been a row inserted
to the table during a definable interval of time (drop anchor when the
"connection" begins, raise anchor when the "connection" ends), and be
independant of the other connections inserting rows to the same table.

Any idaeas?

CG

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Query planner: current_* vs. explicit date

[Chris Gamache]

PsotgreSQL 7.2.4:

Query planner is behaving strangely. It operates differently for explicit dates
and derived dates... any ideas on why? ( and why I might not have noticed this
before... )

CREATE TABLE trans_table (
  id serial, 
  user_name varchar(50), 
  trans_type varchar(50), 
  trans_data varchar(50), 
  trans_date timestamptz, 
  trans_uuid uniqueidentifier, 
  CONSTRAINT trans_table_pkey PRIMARY KEY (id)
) WITH OIDS;

... Insert lots of data ...

CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date);
CREATE INDEX trans_table_user_date_idx ON trans_table USING btree
(user_name,trans_date);
CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid);

VACUUM ANALYZE trans_table;

EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp;
Seq Scan on trans_table  (cost=0.00..177369.52 rows=315267 width=4)

EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00
AM'::timestamp;
Index Scan using trans_table_date_idx on trans_table  (cost=0.00..1474.69
rows=417 width=4)

CG

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Query planner: current_* vs. explicit date

[Chris Gamache]

Thanks Tom (and others!) Right-on-the-money, as always...

By giving it a definitive range I was able to coax query planner to use the
index:

SELECT id FROM trans_table WHERE trans_date >=  (SELECT
current_date::timestamptz) AND trans_date < (SELECT current_timestamp);

gave me from midnight to the present...

Aside from a slight amount of ugliness, the solution is quite adequate. The
subselects shouldn't cause too much overhead, yes?

BTW, This didn't work:

SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND
trans_date < current_timestamp;

Which was a "nonconstant" version of the above. I think it still suffers from
the timestamp >= unknown_value problem.

CG

--- Tom Lane <[EMAIL PROTECTED]> wrote:
> being careful that both comparison values are nonconstant (don't use
> 'infinity'::timestamp, for instance, even though that might seem like
> a reasonable thing to do).  The planner still has no idea how many rows
> will be fetched exactly, but it does realize that this is a range
> condition, and its default assumption about the number of matching rows
> is small enough to encourage indexscan use.
> 
> Of course this workaround assumes that you can pick an upper bound that
> you are sure is past the end of the available values, but that's usually
> not hard in the sort of context where you would have thought that the
> one-sided inequality test is a sane thing to do anyway.


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Historic Query using a view/function ?

[Chris Gamache]

...Postgresql 7.2...

I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared in a point in time. I
can't seem to wrap my brain around the problem, tho.

Given some tables

CREATE TABLE list (
  num int4 NOT NULL, 
  name varchar(50), 
  type varchar(50), 
  modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone, 
  CONSTRAINT list_pkey PRIMARY KEY (num)
) WITH OIDS;

CREATE TABLE list_log (
  num int4 NOT NULL, 
  name varchar(50), 
  type varchar(50), 
  modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone, 
  mod_type varchar(3), 
  log_date timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone
) WITH OIDS;

And some rules...

CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");

CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");

It'd be great to be able to do something like...

SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';

... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?

CG


__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Historic Query using a view/function ?

[Chris Gamache]

...Postgresql 7.2...

I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared at a point-in-time. I
can't seem to wrap my brain around the problem, tho.

Given some tables

CREATE TABLE list (
  num int4 NOT NULL, 
  name varchar(50), 
  type varchar(50), 
  modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone, 
  CONSTRAINT list_pkey PRIMARY KEY (num)
) WITH OIDS;

CREATE TABLE list_log (
  num int4 NOT NULL, 
  name varchar(50), 
  type varchar(50), 
  modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone, 
  mod_type varchar(3), 
  log_date timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone
) WITH OIDS;

And some rules...

CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");

CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");

It'd be great to be able to do something like...

SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';

... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?

CG



__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] tsearch2 trigger alternative

[Chris Gamache]

Tsearch2 comes with its own tsearch2 trigger function. You pass column names to
it, and it puts a vanilla tsvector into the column names in TG_ARGV[0] (zero
based, yes?). Not only can you pass column names to it, but you can pass simple
functions to it as well. This is magical to me. :)

I'm trying to figure out how to do the same thing, except instead of returning
a vanilla tsvector, I want to return a specially weighted tsvector. I've
created a function that can do this:

create or replace function name_vector (text) returns tsvector as '
select setweight(to_tsvector(substr($1,1,strpos($1,'',''))),''C'') ||
to_tsvector(substr($1,strpos($1,'','')+1,length($1)));
' language 'sql';

so... 

Plain:

select to_tsvector('Einstein, Albert');
   to_tsvector
-
 'albert':2 'einstein':1

Weighted:

select name_vector('Einstein, Albert');
   name_vector
--
 'albert':2 'einstein':1C


Now, to somehow package that into a magical trigger function... 

All the examples for creating trigger functions that I've found use static
column names, NEW and OLD ... I would like to create a generic trigger
function, as the tsearch2 trigger function does, to return the specially
weighted tsvector.

Its like a lighter to a caveman. Can anyone lend a hand?

CG

__
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools

---(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

[SQL] SCHEMA's the easy way?

[Chris Gamache]

I'm planning on dipping my toes into the world of schemata. I have tables,
created in the Public schema, that I'd like to move to the new schema:

SELECT * INTO new.tablename FROM public.tablename;
CREATE SEQUENCE ...;
CREATE INDEX ...;
ALTER TABLE ...;
BLAH ...;
BLAH ...;
BLAH ...;
DROP public.tablename;
REPEAT ...;
REPEAT ...;
REPEAT ...;
VOMIT;

Is there an easier, faster, less user-error-prone way around this? 




__
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Schemata & User-Defined-Type casting issues

[Chris Gamache]

PostgreSQL 7.4.2 ...

Background: I'm attempting to migrate tables which were created in the
pre-schema days to a sensible schema setup. I'm using the "uniqueidentifier"
column in some of these tables. When I created the new schema, I created an
instance of "uniqueidentifier" and its supporting functions and casts within
the new schema. When I try to "INSERT INTO myschema.mytable ... SELECT ... FROM
public.mytable;" It's having difficulty seeing that the data types are
compatible across the schema. An explicit cast (without first casting to a
neuter data-type) won't work for the same reason.

I'm torn: Should I create a "cast" to allow for casting of this data-type
across schemas, or should I have created the table referencing the user-defined
type in the public schema? 

I expect that this problem will rise up now and again. I'd like to solve it in
the this early phase with a proper deisgn-based fix.

If it makes a difference, I would like to not include this schema in the search
path, to explicitly refer to it as myschema.mytable anywhere I need to
reference it.

CG




__
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/ 

---(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

[SQL] Schema + User-Defined Data Type Indexing problems...

[Chris Gamache]

PostgreSQL 7.4.2 -- All vacuumed and analyzed.

I inserted the uniqueidentifier datatype into a new schema that I'm working on
by changing the search_path to "my_schema" in the contrib SQL. It effectively
created the datatype within the schema, all of its functions, operators, and
operator classes. To move the data from the public schema into the new
"my_schema" I had to create an assignment cast public.uniqueidentifier to
my_schema.uniqueidentifier. I was profiling queries and I couldn't figure out
why PostgreSQL wasn't using indexes. I'm having a heck of a time, and it seems
like in my thrashing about to find a solution to this problem I have ruined the
uniqueidentifier datatype in the schema...

CREATE INDEX mt_uuid_idx
  ON my_schema.my_table USING btree (my_uuid);

ERROR:  data type my_schema.uniqueidentifier has no default operator class for
access method "btree"
HINT:  You must specify an operator class for the index or define a default
operator class for the data type.

I can look at the operator classes and see that there is an operator class for
btree for my_schema.uniqueidentifier.

I must be doing something wrong with my schema set-up to have this much trouble
with it. If this is the norm for complexity when using schema, I'm not sure it
is worth the effort to impliment. Other PostgreSQL users are comfortable with
the schema implimentation... I _must_ be doing something wrong. The bottom line
for my problem is that searches that should be using indexes in the schema
aren't. Please help me find out what's going on.

CG




__
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/ 

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

[Chris Gamache]

--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Chris Gamache <[EMAIL PROTECTED]> writes:
> > I'm having a heck of a time, and it seems like in my thrashing about
> > to find a solution to this problem I have ruined the uniqueidentifier
> > datatype in the schema...
> 
> > CREATE INDEX mt_uuid_idx
> >   ON my_schema.my_table USING btree (my_uuid);
> 
> > ERROR:  data type my_schema.uniqueidentifier has no default operator class
> for
> > access method "btree"
> > HINT:  You must specify an operator class for the index or define a default
> > operator class for the data type.
> 
> > I can look at the operator classes and see that there is an operator class
> for
> > btree for my_schema.uniqueidentifier.
> 
> IIRC, the opclass has to be in a schema that is in your schema search
> path to be found by CREATE INDEX by default.  If it isn't, you could
> specify it explicitly:
> 
> CREATE INDEX mt_uuid_idx
>   ON my_schema.my_table USING btree (my_uuid USING my_schema.uuidopclass);
> 
> It's possible that we could think of a more convenient behavior for
> default opclasses, but I don't want to do something that would foreclose
> having similarly-named datatypes in different schemas.  You have any
> suggestions?

That /is/ important to be able to have similarly named datatypes in different
schemas. I'll give the explicit opclass a go. Indeed, if I place the schema in
my search path the index creation and index scans seem to work perfectly. I had
wanted to have to specify the schema whenever I referenced objects in it
instead of putting it in my search path. I had no concept of exactly how truly
separated schemas are. The only idea that I can think of (and, again, I may be
underestimating the level of separation that needs to exist between schema) is
that object creation could implicitly looks to the current schema for a usable
index/opclass/whatever first before checking the search path. A SELECT could
look first to the schema of the table before checking the search path for a
usable index. Is it even possible to create an index that lives in a different
schema from the table it is indexing?

CG




__
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/ 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

[Chris Gamache]

--- Tom Lane <[EMAIL PROTECTED]> wrote:
> In practice I'm not sure that this is really a situation that we need to
> fret about, because using a datatype that isn't in your search path has
> got notational problems that are orders of magnitude worse than this
> one.  The functions and operators that do something useful with the
> datatype would also have to be schema-qualified every time you use them.
> This is perhaps tolerable for functions but it's quite unpleasant for
> operators :-(  You can't write
>   select * from foo where my_uuid = 'xxx';
> instead
>   select * from foo where my_uuid operator(my_schema.=) 'xxx';
> Yech.  I think you'll end up putting uuid's schema in your search path
> before long anyway.

Right you are. I guess the moral of the story is that when using custom
datatypes, search_path is a required setting. I guess that is why the "public"
schema should be just that, completely accessable by any user with rights to
the DB. So, is the best-practice for the my_schema tables to reference the
user-defined datatype in the "public" schema?

CREATE TABLE my_schema.foo (uuid public.uniqueidentifier);





__
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/ 

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Converting integer to binary

[Chris Gamache]

Once upon a time in PostgreSQL there was a function : bitfromint4 ... Any idea
where it has disappeared to?

You can do

# select B'10101101'::int4;
 int4
--
  173
(1 row)

but you want to go

# select 173::varbit;

which is what bitfromint4 used to do.

CG

--- Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Thu, Jun 10, 2004 at 14:52:41 +0100,
>   Stephen Quinney <[EMAIL PROTECTED]> wrote:
> > 
> > I have searched around but I cannot see any standard way in PostgreSQL
> > to convert from an integer into a binary representation.
> > 
> > Now I have an algorithm to do it so I could write an SQL function, I
> > guess. If there's a standard way to do it though that would be quite nice.
> 
> There doesn't seem to currently be a function that does this. to_char
> would be the logical place since that is what is used to convert various
> numeric types to strings with a decimal representation.
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster





__
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/ 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html