[SQL] partial index on non default tablespace syntax
Hi, Looks like its not possible to specify tablespace of an index with a where clause, we require to create the index, and then use ALTER INDEX for setting the tablespace. Is it something that is already known or its itentional so as to keep the command unambigious. i also could not find it in the TODO Regds Rajesh Kumar Mallah. ---(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
Re: [SQL] partial index on non default tablespace syntax
On Sat, Jun 18, 2005 at 10:24:06PM +0530, Rajesh Kumar Mallah wrote: > > Looks like its not possible to specify tablespace of an index with a > where clause, Could you show the command you're running and the error you get, or otherwise explain what problem you're seeing? Is the following not what you're looking for? CREATE INDEX foo_name_idx ON foo (name) TABLESPACE testspace WHERE name IS NOT NULL; http://www.postgresql.org/docs/8.0/static/sql-createindex.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] partial index on non default tablespace syntax
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > Looks like its not possible to specify tablespace of an index with a > where clause, Hm? regression=# create table foo(f1 int, f2 int); CREATE TABLE regression=# create index fooi on foo (f1) tablespace pg_default where f2 < 0; CREATE INDEX (I hadn't bothered to make a tablespace to test with, but the point is the syntax is fine.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Set Membership operator -- test group membership
Say I have a table tbl1 with two columns:
tbl1(a integer, b integer, c integer)
I want to select the rows in which a and b are members
of a list of integer pairs. The SQL in my mind is
something like:
select * from tbl1 where (a, b) in ((1, 20), (2,
30), (3, 50));
I know the SQL above does not work in PostgreSQL. I
wonder what is the proper way to use in PostgreSQL. I
tried "select * from tbl1 where (a, b) in ('{{1, 20},
{2, 30}, {3, 50}}')", and it doesn't work either.
Thanks!
Sophie
__
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] AYUDA CON LA INSTALACION DEL POSTGRESQL
Inicie la instalacion del PostgreSQL 7.3.10 como dice el install: ./configure gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 & /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test Pero al llegar a la penultima linea me sale: [EMAIL PROTECTED] postgres]$ /usr/local/pgsql/bin/createdb test psql: could not connect to server: No existe el fichero o el directorio Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? createdb: database creation failed Que es lo que debo hacer? TanX __ Discover Yahoo! Find restaurants, movies, travel and more fun for the weekend. Check it out! http://discover.yahoo.com/weekend.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Looking for info on 8.1 features, and some examples
I am looking at the features for pgsql 8.1 (based on what I read in http://gborg.postgresql.org/project/pljava/projdisplay.php), and am wondering if anyone reading this has a simple example of some procedures that use OUT and INOUT parameters that they would be willing to share... Larry ---(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] Dynamic PL/pgSQL
I plan to run an SQL script - using JDBC - to initialize a database. Right now I have the need to execute serveral statements only under certain conditions. for example: IF NOT EXISTS ( SELECT 1 FROM pg_catalog.pg_user WHERE ussername = 'Jon' ) THEN CREATE USER 'Jon'; END IF; Within a PL/pgSQL function this would be easy, but I need to store the complete initialization script in a text file and execute it as a whole. Is there any way to solve this? Any suggestions are welcome TIA Rüdiger -- Geschenkt: 3 Monate GMX ProMail gratis + 3 Ausgaben stern gratis ++ Jetzt anmelden & testen ++ http://www.gmx.net/de/go/promail ++ ---(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
Re: [SQL] [GENERAL] Set Membership operator -- test group membership
Sophie,
The sql like this:
select * from tbl1 where (a, b) in ((1, 20), (2, 30), (3, 50));
works very well in PostgreSQL 8,
Sincerely,
Igor Katrayev, Data Systems Manager
North Pacific Research Board
1007 West Third Avenue, Suite 100
Anchorage, AK 99501
Phone: 907-644-6700
Fax: 907-644-6780
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Sophie Yang
Sent: Tuesday, June 14, 2005 11:09 AM
To: pgsql-general@postgresql.org
Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Subject: [GENERAL] Set Membership operator -- test group membership
Say I have a table tbl1 with two columns:
tbl1(a integer, b integer, c integer)
I want to select the rows in which a and b are members of a list of
integer pairs. The SQL in my mind is something like:
select * from tbl1 where (a, b) in ((1, 20), (2, 30), (3, 50));
I know the SQL above does not work in PostgreSQL. I wonder what is the
proper way to use in PostgreSQL. I tried "select * from tbl1 where (a,
b) in ('{{1, 20}, {2, 30}, {3, 50}}')", and it doesn't work either.
Thanks!
Sophie
__
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(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
Re: [SQL] Putting an INDEX on a boolean field?
In my database, the "sites" table is large, and the "usersites" table
has only a few sites per userid - so it should be looked in first. I'm
surprised that I had to juggle my query around (below), rather than
trusting the optimizer to figure this out for me.
Should I start looking to figure out why the optimizer didn't figure out
that it should be doing this sort of thing? Or should I just keep
juggling with subselects, since it's not that hard to do.
This query results in a sequential scan:
select sites.*, blocks from sites left join quota on
sites.host_u = quota.host and quota.date = ('2005-06-16 23:55:05-04')
where sites.id in (select siteid from usersites where userid = 1);
versus this one which does not:
explain analyze select sites.*, blocks from (select * from sites
where id in (select siteid from usersites where userid = 1)) as sites
leftjoin quota on sites.host_u = quota.host and quota.date =
('2005-06-1623:55:05-04');
The tables have been vacuumed/analyzed, etc.
---slow---
explain analyze select sites.*, blocks from sites left join quota on
sites.host_u = quota.host and quota.date = ('2005-06-16 23:55:05-04')
where sites.id in (select siteid from usersites where userid = 1);
---
Hash IN Join (cost=3183.30..3295.50 rows=13 width=158) (actual
time=4865.895..4942.097 rows=10 loops=1)
Hash Cond: ("outer".id = "inner".siteid)
-> Merge Left Join (cost=3173.52..3263.12 rows=4493 width=158)
(actual time=4856.212..4939.329 rows=4443 loops=1)
Merge Cond: ("outer"."?column24?" = "inner".host)
-> Sort (cost=2786.62..2797.72 rows=4443 width=154) (actual
time=4811.499..4816.164 rows=4443 loops=1)
Sort Key: (sites.host_u)::text
-> Seq Scan on sites (cost=0.00..2517.43 rows=4443
width=154) (actual time=11.611..4598.849 rows=4443 loops=1)
-> Sort (cost=386.91..398.13 rows=4489 width=26) (actual
time=44.638..46.307 rows=4454 loops=1)
Sort Key: quota.host
-> Index Scan using quota_date_idx on quota
(cost=0.00..114.60 rows=4489 width=26) (actual time=0.069..10.780
rows=4453 loops=1)
Index Cond: (date = '2005-06-16
23:55:05-04'::timestamp with time zone)
-> Hash (cost=9.75..9.75 rows=12 width=4) (actual time=0.086..0.086
rows=0 loops=1)
-> Index Scan using usersites_userid_idx on usersites
(cost=0.00..9.75 rows=12 width=4) (actual time=0.047..0.076 rows=10
loops=1)
Index Cond: (userid = 1)
Total runtime: 4944.575 ms
(15 rows)
---fast---
explain analyze select sites.*, blocks from (select * from sites where
id in (select siteid from usersites where userid = 1)) as sites left
join quota on sites.host_u = quota.host and quota.date = ('2005-06-16
23:55:05-04');
---
Merge Left Join (cost=468.77..491.41 rows=13 width=158) (actual
time=46.547..53.669 rows=10 loops=1)
Merge Cond: ("outer"."?column24?" = "inner".host)
-> Sort (cost=81.86..81.89 rows=12 width=154) (actual
time=0.450..0.454 rows=10 loops=1)
Sort Key: (public.sites.host_u)::text
-> Nested Loop (cost=9.78..81.65 rows=12 width=154) (actual
time=0.129..0.392 rows=10 loops=1)
-> HashAggregate (cost=9.78..9.78 rows=12 width=4)
(actual time=0.084..0.095 rows=10 loops=1)
-> Index Scan using usersites_userid_idx on
usersites (cost=0.00..9.75 rows=12 width=4) (actual time=0.040..0.067
rows=10 loops=1)
Index Cond: (userid = 1)
-> Index Scan using sites_pkey on sites
(cost=0.00..5.98 rows=1 width=154) (actual time=0.017..0.018 rows=1
loops=10)
Index Cond: (sites.id = "outer".siteid)
-> Sort (cost=386.91..398.13 rows=4489 width=26) (actual
time=44.971..46.042 rows=3741 loops=1)
Sort Key: quota.host
-> Index Scan using quota_date_idx on quota
(cost=0.00..114.60 rows=4489 width=26) (actual time=0.025..10.643
rows=4453 loops=1)
Index Cond: (date = '2005-06-16 23:55:05-04'::timestamp
with time zone)
Total runtime: 54.988 ms
(15 rows)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] WHY transaction waits for another transaction?
Hallo I'm porting one Oracle Application to Postgres. This Programm uses "long living" Transactions. During the execution of transaction Nr:10295 (PID:18430) one new transaction with Nr:10339 (PID:18431) starts, that writes one record into the table. But this new transaction never stops, because it tries to set one ShareLock to its parrent transaction Nr:10295. My problem is, how can i found out - WHY the second transaction waits for end of first transaction? Is there a tool for analyzing such deadlocks? pg_lock output relation database transacti pid modegranted 25155325133118430AccessShareLock t 25155325133118430RowExclusiveLockt 25148725133118430AccessShareLock t 25148725133118430RowShareLockt 25148725133118430RowExclusiveLockt 25149225133118430AccessShareLock t 25149425133118431AccessShareLock t 25143425133118434AccessShareLock t 25140725133118431AccessShareLock t 25143425133118430AccessShareLock t 25142925133118430AccessShareLock t 25154825133118430AccessShareLock t 25154825133118430RowExclusiveLockt 25157525133118430AccessShareLock t 25145825133118430AccessShareLock t 25152425133118434AccessShareLock t 1029318434ExclusiveLock t 25148225133118430AccessShareLock t 25148225133118430RowShareLockt 25149225133118431AccessShareLock t 25157725133118431AccessShareLock t 25147725133118431AccessShareLock t 25156725133118431AccessShareLock t 25141725133118431AccessShareLock t 25149225133118434AccessShareLock t 1247 25133118431AccessShareLock t 16839 25133118438AccessShareLock t 25149425133118430AccessShareLock t 25149425133118430RowShareLockt 25149425133118430RowExclusiveLockt 25143425133118431AccessShareLock t 25155325133118431AccessShareLock t 25149425133118434AccessShareLock t 25139225133118431AccessShareLock t 25147225133118430AccessShareLock t 25147225133118430RowShareLockt 25152625133118431AccessShareLock t 25157725133118430AccessShareLock t 25148225133118431AccessShareLock t 25148225133118431RowShareLockt 25147225133118431AccessShareLock t 25147225133118431RowShareLockt 25147225133118431RowExclusiveLockt 25148725133118434AccessShareLock t 25155325133118434AccessShareLock t 25140725133118430AccessShareLock t 25140725133118430RowShareLockt 1038118438ExclusiveLock t 25148725133118431AccessShareLock t 25148725133118431RowShareLockt 25148725133118431RowExclusiveLockt 25150925133118430AccessShareLock t 25138625133118431AccessShareLock t 25139425133118430AccessShareLock t 1033918431ExclusiveLock t 25140725133118434AccessShareLock t 1029518430ExclusiveLock t 25142225133118430AccessShareLock t 1029518431ShareLock f _ Mit der Gruppen-SMS von WEB.DE FreeMail können Sie eine SMS an alle Freunde gleichzeitig schicken: http://freemail.web.de/features/?mc=021179 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Returning a Cross Tab record set from a function
Hi All, thanks for your responses.
I know higher level languages can perform the operation, but I think a function written in a higher level language could still not return a resulting (structure undefined) table back as a result set. I hope I am wrong about this ;-) If not, read on...
My function caller cannot query like SELECT * FROM crosstab ('SELECT x,y,z FROM foo ...') AS ct(a int, b text, c text) because he does not know that x,y,or z are available to him, and there may also q,r, and s too. That's part of what he is hoping to get from the query! (as well as the data for these columns)
Instead I need to query like SELECT * FROM crosstab_undef ('SELECT * FROM foo' ...)
I am no programmer, so I need someone to tell me if it is possible to add this feature to Postgres, or does it go against every (type) rule written? The difference is that if it IS possible, I may be able to get someone to write some code to contribute. I would love to hear from a developer on this one.
I have a system that has two interfaces to the database (More if you include reporting tools) and I would like similar functionality for all interfaces. This is why I would like to have a function defined at the database level.
Many Thanks,
Marc Wrubleski
On Mon, 2005-06-06 at 12:20 +0200, KÖPFERL Robert wrote:
|-Original Message-
|From: Marc Wrubleski [mailto:[EMAIL PROTECTED]]
|Sent: Mittwoch, 01. Juni 2005 16:15
|To: pgsql-sql@postgresql.org
|Subject: [SQL] Returning a Cross Tab record set from a function
|
[...]
|
|It seems I can do this from any higher level language, but it drives me
|crazy that I can't perform this operation as a function inside of
|Postgres...
Yes, semms very like this can't be done in just sql but needs a higher level
lng.
Actually Postgres provides such higher languages. Try including PL/Python
PL/perl or PL/tk. Fom there you are able to query all the metadata of the
wanted tables so that a adequate SQL-string can be generated.
In case of sourcecode, you find theses languages in the contrib dir
|
|Thanks for any thoughts you might have...
|
|--
|Marc Wrubleski
|
|
|---(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
|
--
Marc Wrubleski <[EMAIL PROTECTED]>
Re: [SQL] partial index on non default tablespace syntax
Fuhr,Tom and Everyone Extremely sorry for not consulting the docs. I was doing this: CREATE INDEX foo_name_idx ON foo (name) WHERE name IS NOT NULL TABLESPACE testspace ; Regds Rajesh Kumar Mallah. On 6/18/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sat, Jun 18, 2005 at 10:24:06PM +0530, Rajesh Kumar Mallah wrote: > > > > Looks like its not possible to specify tablespace of an index with a > > where clause, > > Could you show the command you're running and the error you get, > or otherwise explain what problem you're seeing? Is the following > not what you're looking for? > > CREATE INDEX foo_name_idx ON foo (name) > TABLESPACE testspace > WHERE name IS NOT NULL; > > http://www.postgresql.org/docs/8.0/static/sql-createindex.html > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(end of broadcast)--- TIP 8: explain analyze is your friend
